sql - 需要将查询从 Oracle 转换为 SQL Server，并且需要转换 ListAgg

Question

我需要转换下面的查询以供 SQL Server 使用。任何想法如何转换此 ListAgg 查询以供 SQL Server 使用？

select A.CONTRACT_ID,A.CONTRACT_ref_num,A.BA_INT_ID,LISTAGG(B.WHOLE_NAME,' | ')  WITHIN GROUP (ORDER BY A.CONTRACT_ID DESC) Distributors
into #Distributors from MN_BA_INT A, MN_BA_INT_WHOLE B, MN_STRUCTURED_DOC C 
where 
A.CONTRACT_ID=C.struct_doc_id 
AND A.BA_INT_ID=B.BA_INT_ID 
group by A.CONTRACT_ID,A.CONTRACT_ref_num,A.BA_INT_ID

标签： sqloraclesql-server-2012listagg

Answer 1

你可以试试这个。

;WITH CTE AS (
    select 
        A.CONTRACT_ID,
        A.CONTRACT_ref_num,
        A.BA_INT_ID,
        B.WHOLE_NAME
    from 
        MN_BA_INT A
        INNER JOIN MN_BA_INT_WHOLE B ON A.BA_INT_ID=B.BA_INT_ID 
        INNER JOIN MN_STRUCTURED_DOC C ON A.CONTRACT_ID=C.struct_doc_id 
)
SELECT CONTRACT_ID, CONTRACT_ref_num, BA_INT_ID, 
    STUFF( ( SELECT  ' | ' + WHOLE_NAME 
        FROM CTE T2 
    WHERE T1.CONTRACT_ID = T2.CONTRACT_ID 
        AND T1.CONTRACT_ref_num = T2.CONTRACT_ref_num 
        AND T1.BA_INT_ID = T2.BA_INT_ID 
    ORDER BY CONTRACT_ID DESC FOR XML PATH('') ) ,1,3,'') AS Distributors FROM CTE T1   
group by 
    CONTRACT_ID, CONTRACT_ref_num, BA_INT_ID