sql-server - 来自子查询的 SQL 内容

Question

我有一个返回服务和 ContractorID 列表的查询。我需要将这些服务填充到一个字段中，以便将它们与 ContractorID 的另一个选择语句连接起来，但我不知道该怎么做。

列出服务的选择是“

    SELECT DISTINCT SM.ContractorID,

CASE WHEN S.bitRestrictedSelection = 1 
        THEN S.vchDescription + '*'
        ELSE S.vchDescription
    END AS vchDescription
FROM tblAscServiceRegionToOperator SRTO 
INNER JOIN tblServiceMatrix SM 
    ON SRTO.OperatorID = 12624
    AND SM.ServiceRegionID = SRTO.ServiceRegionID
    AND SM.bitPrimaryService = 1
INNER JOIN tblServices S
    ON S.ServiceID = SM.ServiceID

这会产生以下内容:

在示例中，对于 Contractor #16 He has 4 services 我需要通过加入另一个 Select 语句将它们放在一个名为 services 的字段中我尝试了以下操作，但出现错误:

    Select DISTINCT CompanyID, vchCompanyName as CompanyName,vchFIDNumber,vchPrimContactName, vchPrimContactEmail 
,stuff((','
    SELECT DISTINCT 
    SM.ContractorID,

        CASE WHEN S.bitRestrictedSelection = 1 
            THEN S.vchDescription + '*'
            ELSE S.vchDescription
        END AS vchDescription
    FROM tblAscServiceRegionToOperator SRTO 
    INNER JOIN tblServiceMatrix SM 
        ON SRTO.OperatorID = 12624
        AND SM.ServiceRegionID = SRTO.ServiceRegionID
        AND SM.bitPrimaryService = 1
    INNER JOIN tblServices S
        ON S.ServiceID = SM.ServiceID
        FOR XML PATH('')
                ), 1, 1, '') as Services from tblCompany

非常感谢任何帮助!!!

Answer 1

下面的查询会起作用:

SELECT SS.contractor Contractor,
       STUFF((SELECT '; ' + US.vchdescription 
              FROM ServicesList US
              WHERE US.contractor = SS.contractor
              FOR XML PATH('')), 1, 1, '') [Services]
FROM ServicesList SS
GROUP BY SS.contractor
ORDER BY 1

我创建了一个表并在其中插入了两行，contractor = 16，以便您理解。您可以使用评论中给您的建议，将第一个选择包装到 CTE 中，然后在该 CTE 上执行 STUFF 函数.

您可以查看此查询的演示 here .