Solved

Creating SQL View - Combining rows into one comma delimited row

Posted on 2014-03-12
5
353 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now