SQL Server 2012 的逗号分隔列表输出，包含许多联接

Question

我一直在SO中寻找这个答案，但找不到。

我有以下 SQL：

SELECT DISTINCT RM.RegulatoryModel, AA.Agency
FROM Search_By_PartNumber SBPN
LEFT JOIN RegulatoryModels_PartNumbers RMPN ON SBPN.PartNumber = RMPN.PartNumber 
LEFT JOIN RegulatoryModels RM ON RMPN.RegModID = RM.RegModID 
LEFT JOIN Certifications C ON RM.RegModID = C.RegModID 
LEFT JOIN Agencies AA ON AA.AgencyID = C.AgencyID
WHERE RMPN.Active = 1 AND RM.Active = 1
Order BY RegulatoryModel

生成以下数据：

RegulatoryModel Agency
DPS-750XB XX    BSMI
DPS-750XB XX    CE Self Declaration
DPS-750XB XX    CQC
DPS-750XB XX    KTC
DPS-750XB XX    Kvalitet
DPS-750XB XX    Nemko
DPS-750XB XX    UL

我试图将所有机构放在一行中，例如"BSMI，CE 自我声明，..."，我已经找到了如何使用stuff和FOR XML PATH(下面的代码)做到这一点，但它返回了在机构表中找到的每个机构，而不仅仅是与此连接相关的机构。 阅读CROSS APPLY，但也无法弄清楚。

不太正确的代码：

SELECT DISTINCT RM.RegulatoryModel,
AgencyList = stuff((
SELECT ', ' + A.Agency
FROM Agencies A
order by A.Agency
FOR XML PATH('')
), 1, 2, '')
FROM Search_By_PartNumber SBPN
LEFT JOIN RegulatoryModels_PartNumbers RMPN ON SBPN.PartNumber = RMPN.PartNumber 
LEFT JOIN RegulatoryModels RM ON RMPN.RegModID = RM.RegModID 
LEFT JOIN Certifications C ON RM.RegModID = C.RegModID 
WHERE RMPN.Active = 1 AND RM.Active = 1
Order BY RegulatoryModel

关于如何修改上述代码以仅提供在此多级 JOIN 中匹配的机构的逗号分隔列表的建议？

Answer 1

尝试先包装在CTE中，然后再填充它。

;
WITH    CTE
AS ( 

SELECT DISTINCT RM.RegulatoryModel, AA.Agency
FROM Search_By_PartNumber SBPN
LEFT JOIN RegulatoryModels_PartNumbers RMPN ON SBPN.PartNumber = RMPN.PartNumber 
LEFT JOIN RegulatoryModels RM ON RMPN.RegModID = RM.RegModID 
LEFT JOIN Certifications C ON RM.RegModID = C.RegModID 
LEFT JOIN Agencies AA ON AA.AgencyID = C.AgencyID
WHERE RMPN.Active = 1 AND RM.Active = 1
Order BY RegulatoryModel

)
SELECT DISTINCT
RM.RegulatoryModel ,
AgencyList = STUFF(( SELECT ', ' + A.Agency
FROM   CTE A
ORDER BY A.Agency
FOR
XML PATH('')
), 1, 2, '')
FROM    CTE RM

更新：

;
WITH    CTE
AS ( 

SELECT DISTINCT RM.RegulatoryModel, AA.Agency,SBPN.PartNumber
FROM Search_By_PartNumber SBPN
LEFT JOIN RegulatoryModels_PartNumbers RMPN ON SBPN.PartNumber = RMPN.PartNumber 
LEFT JOIN RegulatoryModels RM ON RMPN.RegModID = RM.RegModID 
LEFT JOIN Certifications C ON RM.RegModID = C.RegModID 
LEFT JOIN Agencies AA ON AA.AgencyID = C.AgencyID
WHERE RMPN.Active = 1 AND RM.Active = 1

)
SELECT DISTINCT
RM.RegulatoryModel ,
AgencyList = STUFF(( SELECT ', ' + A.Agency
FROM   CTE A
WHERE RM.PartNumber = A.PartNumber
ORDER BY A.Agency
FOR
XML PATH('')
), 1, 2, '')
FROM    CTE RM

Answer 2

请注意原始查询中的以下ON子句：

SELECT DISTINCT RM.RegulatoryModel, AA.Agency
FROM Search_By_PartNumber SBPN
LEFT JOIN RegulatoryModels_PartNumbers RMPN ON SBPN.PartNumber = RMPN.PartNumber 
LEFT JOIN RegulatoryModels RM ON RMPN.RegModID = RM.RegModID 
LEFT JOIN Certifications C ON RM.RegModID = C.RegModID 
LEFT JOIN Agencies AA ON AA.AgencyID = C.AgencyID
--^--This one
WHERE RMPN.Active = 1 AND RM.Active = 1
Order BY RegulatoryModel

请注意，您的第二个查询中没有类似的条件吗？我们需要使用以下条件关联您的子查询：

SELECT DISTINCT RM.RegulatoryModel,
AgencyList = stuff((
SELECT ', ' + A.Agency
FROM Agencies A
WHERE A.AgencyID = C.AgencyID
order by A.Agency
FOR XML PATH('')
), 1, 2, '')
FROM Search_By_PartNumber SBPN
LEFT JOIN RegulatoryModels_PartNumbers RMPN ON SBPN.PartNumber = RMPN.PartNumber 
LEFT JOIN RegulatoryModels RM ON RMPN.RegModID = RM.RegModID 
LEFT JOIN Certifications C ON RM.RegModID = C.RegModID 
WHERE RMPN.Active = 1 AND RM.Active = 1
Order BY RegulatoryModel

如果这不能为您提供正确的输出(您仍然有比您想要的更多的行，并且没有足够的项目被连接)，则可能表明需要将更多的表/联接向上移动到子查询中。但请记住，您需要确保它与外部查询保持相关。

请注意，正如 Esperento57 在评论中提到的，外部WHERE子句有点可疑(迫使某些连接有效地成为INNER连接)。

此外，我会在逗号分隔的数据周围插入我通常的警告 - 这不是在 T-SQL中表示多个值的自然方式。有些类型设计用于保存多个值(从 2016 年开始的表、xml、json)，CSV 字符串不是其中之一。理想情况下，我会将这种转换留给表示层，而不是将其放入数据库中。