• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 371
  • Last Modified:

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!
0
LCNW
Asked:
LCNW
  • 4
1 Solution
 
knightEknightCommented:
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
 
knightEknightCommented:
nuts, i posted then realized the data might not be right ... stand by, working it.
0
 
knightEknightCommented:
-- 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
 
knightEknightCommented:
-- 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
 
LCNWAuthor Commented:
Thank you!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now