Solved

Variation on comma-separated strings in T-SQL

Posted on 2013-05-10
5
157 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 250 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

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, …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

685 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