sql - 数据透视查询中的 CSV 值

Question

这是我的 table

Manger  EmpID
ABC     5
ABC     6
XYZ     2
XYZ     3

如果我使用数据透视查询，我得到的结果低于输出。

 ABC    XYZ
 6      3

但实际上我想要这样的输出。

  ABC   XYZ
  5,6   2,3

我不想要聚合值，我想要 csv 值。谁能帮我得到这样的输出。这是我的 sql 代码

    Select * 
INTO #TEMP
FROM 
(SELECT 'ABC' As Manger , 5 as EmpID
union
SELECT 'ABC' As Manger , 6 as EmpID
union
SELECT 'XYZ' As Manger , 2 as EmpID
union
SELECT 'XYZ' As Manger , 3 as EmpID
) A

select * 
FROM #TEMP



select * 
FROM #TEMP
PIVOT
(
    MAX([EmpID])
    FOR Manger IN ([ABC],[XYZ])
) as P

drop table #TEMP

Answer 1

试试这个:

SELECT * 
FROM (
   SELECT DISTINCT Manager, 
          STUFF((SELECT ',' + CAST(EmpID AS VARCHAR(MAX)) AS [text()]
                 FROM #TEMP AS t2
                 WHERE t1.Manager = t2.Manager
                 FOR XML PATH('')), 1, 1, '') AS EmpID
   FROM #TEMP AS t1) AS SRC       
PIVOT
(
    MAX([EmpID])
    FOR Manager IN ([ABC],[XYZ])
) as P

诀窍是在应用 PIVOT 之前预处理您的表。上面的查询使用 FOR XML PATH为每个 Manager 创建一个 EmpID 值的逗号分隔列表。

Demo here