Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Creating SQL View - Combining rows into one comma delimited row

Posted on 2014-03-12
5
Medium Priority
?
369 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 2000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

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 …
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

916 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