Sample table TB data is as follows
ID value
—————
1 AA
1 BB
2 AAA
2 BBB
2 CCC
First Kind
SELECTID,[Val]=(SELECT [value] +',' fromTb asbWHEREb.ID=a.id forXML PATH ("') ) fromTb asA
The first display of results
1 AA,BB,
1 AA,BB,
2 AAA,BBB,CCC,
2 AAA,BBB,CCC,
2 AAA,BBB,CCC,
The second Kind
SELECTID,[Val]=(SELECT [value] +',' fromTb asbWHEREb.ID=a.id forXML PATH ("') ) fromTb asaGROUP byId
The second kind of display
1 AA,BB,
2 AAA,BBB,CCC,
Third (replace the comma of the first end with the stuff function)
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
The third kind of display results
1 AA,BB
2 AAA,BBB,CCC
The STUFF function inserts a string into another string. It removes the specified length of characters from the starting position in the first string, and then inserts the second string at the beginning of the first string.
STUFF (character_expression, start, length, Character_expression_insert)
Fourth (replace all spaces with the Replace function as commas)
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
The result is the same as the third.
SQL merge multiple rows query to row