(1)針對單個table,多行轉成一行,用逗號進行分隔,具體實現如下:
select id,[values]=stuff((select ','+[value] from tb t for xml path('')), 1, 1, '')
from tb
group by id
(2)針對多個table關聯,多行轉成一行,用逗號進行分隔,具體實現如下:
SELECT B.ID2
,[notes]=stuff((SELECT ','+[notes] FROM T1 D
INNER JOIN T2 E ON D.ID1=E.ID2
INNER JOIN T3 F ON E.ID2=F.ID3
WHERE E.ID2=B.ID2
FOR XML PATH('')) , 1, 1, '')
FROM T1 A
INNER JOIN T2 B ON A.ID1=B.ID2
INNER JOIN T3 C ON B.ID2=C.ID3
GROUP BY B.ID2
說明:這是我在工作中遇到多個table關聯,多行轉成一行的問題,很多博客都是針對單個table,這裡整理一下分享出來。希望能幫到大家。