将一列中的所有值与SQL Server中具有逗号分隔值的相同ID组合在一起

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:

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.

我希望有人可以帮我解决这个具体问题。

1 个解决方案

#1

This could be done using FOR XML PATH()

这可以使用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