CODEWITHSUNDEEP
sqlt-sql
Rod
Rod

Reputation: 15475

How to denormalize rows into csv

I have the following table:

id, name
---------------
1, Customer 01
1, Customer 02
2, Customer 01
3, Customer 01
3, Customer 03

Is there an easy way to get result set like so:

id, customers
-----------------------
1, [Customer01],[Customer02]
2, [Customer01]
3, [Customer01,[Customer03]

I don't really need the brackets.

Upvotes: 0

Views: 265

Answers (1)

Vasily
Vasily

Reputation: 5782

use for xml path

------------------------------------------------------------
--Create temp table for testing
IF OBJECT_ID('Tempdb..#Temp') IS NOT NULL 
    DROP TABLE #Temp

Create table #Temp
            (id Int, 
             name varchar(30))
Insert into #Temp
Values (1, 'Customer 01'),
       (1, 'Customer 02'),
       (2, 'Customer 01'),
       (3, 'Customer 01'),
       (3, 'Customer 03')

Query

SELECT DISTINCT
        o.id ,
        STUFF((SELECT  ',' + name
               FROM    #Temp AS i
               WHERE   i.ID = o.id
               FOR XML PATH('')),1,1,'') AS ConcatenatedName
FROM    #Temp AS o

Test is here SQL FIDDLE

Upvotes: 1

Related Questions