Solved

Creating SQL View - Combining rows into one comma delimited row

Posted on 2014-03-12
5
355 Views
Last Modified: 2014-03-12
Hi. I am attempting to create a view that displays the following data with the NoteText rows combined into a single comma delimited record (NoteType ‘Other’).

Current Output:

CaseFile      NoteType           NoteText
12345          Other                1
12345          Other                2
12345          Assignment      3
12345          Other                4
 
Desired Output:

CaseFile      NoteText
12345          1, 2, 4

SQL Server 2008 R2.

THANK YOU!
0
Comment
Question by:LCNW
  • 4
5 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 39924832
GO
create view MyView as

with cte as (
  select CaseFile, NoteText
  from MyTable
)

select cte.CaseFile,
 STUFF(
  (select ',' + CONVERT(varchar,cte.NoteText)
   from cte
   for xml path('')
  ), 1, 2, '') as NoteText
from cte
group by CaseFile
GO
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 39924835
nuts, i posted then realized the data might not be right ... stand by, working it.
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 39924864
-- okay, here we go:

GO
create view MyView as

with cte as (
  select CaseFile, NoteText
  from MyTable
)

select P.CaseFile,
 STUFF(
  (select ',' + CONVERT(varchar(80),cte.NoteText)  -- I use 80 here arbitrarily
   from cte
   where P.CaseFile = cte.CaseFile
   group by CaseFile, NoteText
   for xml path('')
  ), 1, 1, '') as NoteText
from MyTable P
outer apply cte
group by P.CaseFile
GO
0
 
LVL 33

Accepted Solution

by:
knightEknight earned 500 total points
ID: 39924906
-- I don't know why I made it so complicated - this is simpler:

GO
create view MyView as
select T1.CaseFile,
       STUFF(
              (select ','+convert(varchar(80),NoteText)
               from MyTable T2
               where T2.CaseFile = T1.CaseFile
               for xml Path('')
       ), 1, 1, '') as NoteText                
  from MyTable T1
 group by T1.CaseFile
GO
0
 
LVL 1

Author Comment

by:LCNW
ID: 39924911
Thank you!
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 rs - Sum each category by month 4 41
SQL Improvement  ( Speed) 14 33
TSQL recursive CTE challenge... 8 29
SQL Recursion 6 20
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question