对于SQL Sever 2012，逗号分隔的多个项目无法正常工作

Question

我使用的是SQL Server 2012(v11.0.5058.0-X64(。我想对多列执行逗号分隔。由于我的SQL Server版本是2012，我不能使用string_agg或ListAgg，所以我正在尝试使用CTE和FOR XML PATH，这对我来说是新的。

输出说明：

假设如果一个staff ID包含多个Cost_Center，则应以逗号分隔各个Staff_ID

当前表格：

Staff_No |  Name  | CostCenter     |  status  |
---------+--------+----------------+----------+
1270     | WASI   | 850110 - CPP   |   Active |
1345     | FAK    | 124600 - CPP   |   Active |
1270     | WASI   | 850870 - BKR   |   Active |

期望输出：

Staff_No |  Name  | CostCenter                   |  status  |
---------+--------+------------------------------+----------+
1270     | WASI   | 850110 - CPP , 850870 - BKR  |   Active |
1345     | FAK    | 124600 - CPP                 |   Active |

我试过这个代码：

WITH CTE_TableName AS 
(
SELECT   
a.Staff_No, a.Name,  
CONVERT(varchar(19), a.COST_CENTER_CODE) + ' - ' + b.COST_CENTER_DESC  as CostCenter,   
CASE a.Active 
WHEN 1 THEN 'Active' 
WHEN 2 THEN 'Inactive' 
END AS status   
FROM
[FAV_VS_STAFF_M] a, [FAV_VS_COST_CENTER_M] b 
)
SELECT 
t0.Staff_No, t0.Name, t0.status,
STUFF((SELECT ',' + t2.CostCenter
FROM CTE_TableName t2
WHERE t2.Staff_No = t0.Staff_No
ORDER BY t2.CostCenter
FOR XML PATH('')), 1, LEN(','), '') AS FieldBs
FROM 
CTE_TableName t0
GROUP BY 
t0.Staff_No , t0.Name, t0.status
ORDER BY 
Staff_No;

我没有得到想要的输出。我哪里错了？请帮我

Answer 1

通常，SELECT .. FOR XML ..的WHERE子句应该使子查询完全依赖于外部查询的GROUP BY子句中列出的列。尝试

WITH CTE_TableName AS (
SELECT   a.Staff_No , a.Name , 
CONVERT(varchar(19), a.COST_CENTER_CODE) + ' - ' + b.COST_CENTER_DESC  as CostCenter,   
CASE a.Active WHEN 1 THEN 'Active' WHEN 2 THEN 'Inactive' END AS status   
from [FAV_VS_STAFF_M] a, [FAV_VS_COST_CENTER_M] b )
SELECT t0.Staff_No, t0.Name , t0.status
, STUFF((
SELECT ',' + t2.CostCenter
FROM CTE_TableName t2
WHERE t2.Staff_No = t0.Staff_No
AND t2.Name = t0.Name AND t2.status = t0.status
ORDER BY t2.CostCenter
FOR XML PATH('')), 1, LEN(','), '') AS FieldBs
FROM CTE_TableName t0
GROUP BY t0.Staff_No , t0.Name, t0.status
ORDER BY Staff_No;

db<gt；小提琴

Answer 2

如果您只想要不同的值，则需要在内部SELECT中使用DISTINCT或GROUP BY子句有很多方法可以做到这一点，最简单的是：

STUFF((SELECT DISTINCT ',' + t2.CostCenter
FROM CTE_TableName t2
WHERE t2.Staff_No = t0.Staff_No
AND t2.Name = t0.Name AND t2.status = t0.status
ORDER BY 1
FOR XML PATH(''), TYPE).value('text()[1]','nvarchar(max)')
, 1, LEN(','), '') AS FieldBs

请注意添加了TYPE和value('text()[1]','nvarchar(max)')，以确保正确地取消XML的标题

正如@Serg所指出的，您还需要正确关联连接AND t2.Name = t0.Name AND t2.status = t0.status

另一种剥猫皮的方法：

STUFF((SELECT DISTINCT ',' + t2.CostCenter
FROM CTE_TableName t2
WHERE t2.Staff_No = t0.Staff_No
AND t2.Name = t0.Name AND t2.status = t0.status
ORDER BY ',' + t2.CostCenter
FOR XML PATH(''), TYPE).value('text()[1]','nvarchar(max)')
, 1, LEN(','), '') AS FieldBs

或者：

STUFF((SELECT ',' + t2.CostCenter
FROM CTE_TableName t2
WHERE t2.Staff_No = t0.Staff_No
AND t2.Name = t0.Name AND t2.status = t0.status
GROUP BY ',' + t2.CostCenter
ORDER BY ',' + t2.CostCenter
FOR XML PATH(''), TYPE).value('text()[1]','nvarchar(max)')
, 1, LEN(','), '') AS FieldBs

请参阅此问题，了解为什么需要这样指定ORDER BY。