Solved

Variation on comma-separated strings in T-SQL

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Where to download and how to install sqldmo.dll 5 82
Help with t-sql finding "latest" record in table 10 36
SQL BULK INSERT Comma Delimited Issue 8 50
Help Required 3 96
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
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, …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

770 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