Combine all values in one column with the same ID with Comma Separated values in SQL Server

Question

I have this current output:

ID   |  Date            | Products  
-------------------------------------  
01   |  02-18-2015      | Product A
02   |  02-18-2015      | Product B
02   |  02-18-2015      | Product C

But I want to have this:

ID   |   Date        | Products  
-------------------------------------  
01   |  02-18-2015   | Product A
02   |  02-18-2015   | Product B, Product C

Some part of the code in SQL:

SELECT  * 
INTO #tempA
FROM
    (SELECT 
        INV.ID, INV.Date
     FROM
        TblA INV) a

SELECT
    b.ID, b.Date, b.Products
INTO #tempB
FROM
    (SELECT  z.ID,z.Date,z.Products
       FROM
           (SELECT
                #tempA.ID, #tempA.Date, PR.Items AS Products 
            FROM #tempA
            INNER JOIN Items PR ON ...   ) z
    ) b

I hope someone can help me solve this specific problem.

You are looking for a [group_concat hack](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server) — StuartLC, Mar 10 '15 at 04:34

score 1 · Accepted Answer · answered Mar 10 '15 at 04:34

This could be done using FOR XML PATH()

WITH SampleData(ID, Date, Products) AS(
    SELECT '01', '02-18-2015', 'Product A' UNION ALL
    SELECT '02', '02-18-2015', 'Product B' UNION ALL
    SELECT '02', '02-18-2015', 'Product C' 
)
SELECT
    t1.ID,
    t1.Date,
    Products = STUFF((
        SELECT ', ' + t2.Products
        FROM SampleData t2
        WHERE t1.Date = t2.Date
        AND t1.ID = t2.ID
        FOR XML PATH('')
    ),1, 2, '')
FROM SampleData t1
GROUP BY t1.ID, t1.Date

In my case how will I do the 'FROM SampleData t1' ? because the temp table is having an error if I put t1 on it — ajbee, Mar 10 '15 at 07:46

Combine all values in one column with the same ID with Comma Separated values in SQL Server

1 Answers1