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

x
?
Solved

Variation on comma-separated strings in T-SQL

Posted on 2013-05-10
5
Medium Priority
?
170 Views
Last Modified: 2013-05-16
I have a SELECT statement which yields data in the following pattern:

Category      Item
------------  -------
Red            A
Red            B
Red            C
Blue           A
Blue           D
Blue           E
Blue           F

Open in new window


I need to instead produce this:

Category      Item
------------  -------
Red           A,B,C
Blue          A,D,E,F

Open in new window


Thanks!
0
Comment
Question by:wlevy
  • 4
5 Comments
 
LVL 16

Assisted Solution

by:Surendra Nath
Surendra Nath earned 500 total points
ID: 39157569
try this

select Y2.Category,(select stuff((select ',' + Item from <yourTable> Y1 where Y1.Category = Y2.Category for xml path('')),1,1,'')
from <yourTable> Y2
group by Y2.Category

Open in new window

0
 

Author Comment

by:wlevy
ID: 39158305
I get an error: The multi-part identifier "Y2.Category" could not be bound. This is on "Y2.Category" immediately following "select"  on line 1.
0
 

Accepted Solution

by:
wlevy earned 0 total points
ID: 39158725
I got it now:

select Y2.Category, stuff((select ',' + Item from <yourTable> Y1 where Y1.Category = Y2.Category for xml path('')),1,1,'')
from <yourTable> Y2

Thanks for pointing me in the right direction,
0
 

Author Comment

by:wlevy
ID: 39158726
Oops, my solution was missing DISTINCT. Should be...

SELECT DISTINCT B.FunctionName, STUFF((SELECT ',' + A.WBSID FROM @table A WHERE A.FunctionName = B.FunctionName FOR XML PATH('')),1,1,'')
FROM @table B
0
 

Author Closing Comment

by:wlevy
ID: 39170734
Neo_Jarvis had the right idea but obviously didn't test his solution, which was buggy.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

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 …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

963 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