Мне нужно преобразовать приведенный ниже запрос для использования 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
Вы можете попробовать это.
;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
