Solved

Creating SQL View - Combining rows into one comma delimited row

Posted on 2014-03-12
5
362 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

622 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