Solved

Variation on comma-separated strings in T-SQL

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

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

Title # Comments Views Activity
Concatenating multiple comments into one row 16 61
Retention Policy for Backups 1 24
Update in Sql 7 30
SQL Server CASE .. WHEN .. IN statement - Syntax issue 4 49
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…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

919 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

13 Experts available now in Live!

Get 1:1 Help Now