Sql Server合并多行詢資料到一行：使用自串連、FOR XML PATH('')、STUFF或REPLACE函數

標籤：data   用法   exp   資料   字元   rom   指定   cte   插入   

樣本表 tb 資料如下

id value
—————
1 aa
1 bb
2 aaa
2 bbb
2 ccc

SELECT id,
[val] = (
SELECT [value] + ‘,‘
FROM tb AS b
WHERE b.id = a.id FOR XML PATH(‘‘)
)
FROM tb AS a

顯示結果
1 aa,bb,
1 aa,bb,
2 aaa,bbb,ccc,
2 aaa,bbb,ccc,
2 aaa,bbb,ccc,

 

SELECT id,
[val]=( SELECT [value] +‘,‘
FROM tb AS b
WHERE b.id = a.id
FOR XML PATH(‘‘) )
FROM tb AS a
GROUP BY id

顯示結果
1 aa,bb,
2 aaa,bbb,ccc,

SELECT id,
[val]=STUFF( (SELECT ‘,‘+[value]
FROM tb AS b
WHERE b.id = a.id
FOR XML PATH(‘‘)) , 1 , 1 , ‘‘ )
FROM tb AS a
GROUP BY id

顯示結果
1 aa,bb
2 aaa,bbb,ccc

STUFF 函數將字串插入另一字串。它在第一個字串中從開始位置刪除指定長度的字元；然後將第二個字串插入第一個字串的開始位置。
STUFF ( character_expression , start , length ,character_expression_insert )

SELECT id,
[val]= REPLACE( (SELECT [value] AS [data()]
FROM tb AS b
WHERE b.id = a.id
FOR XML PATH(‘‘)) , ‘ ‘, ‘,‘)
FROM tb AS a
GROUP BY id

結果與上面一樣。
解析：[data()] 這裡據說是起到一個類似數組的作用，具體用法還要再查。
如果外圍不用REPLACE函數包住，則返回的結果是 aaa bbb ccc ，每項之間有空格，所以最後用REPLACE函數將所有空格替換成逗號。

 

Sql Server合并多行詢資料到一行：使用自串連、FOR XML PATH('')、STUFF或REPLACE函數