asked on

Creating SQL View - Combining rows into one comma delimited row

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!

knightEknight

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

knightEknight

nuts, i posted then realized the data might not be right ... stand by, working it.

knightEknight

-- 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

ASKER CERTIFIED SOLUTION

knightEknight

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LCNW

ASKER

Thank you!