SQL STUFF函数 拼接字符串

create table tb(id int, val varchar(10))

insert into tb values(1,'aa')


insert into tb values(1,'bb')


insert into tb values(2,'aaa')


insert into tb values(2,'bbb')


insert into tb values(2,'ccc')


go


 


/*         stuff(param1, startIndex, length, param2)
说明：将param1中自startIndex(SQL中都是从1开始，而非0)起，删除length个字符，然后用param2替换删掉的字符。*/


SELECT id,
val = stuff(
(
SELECT ',' + val FROM tb AS t WHERE t.id = tb.id FOR xml path('')
), 1, 1, ''
)
FROM tb GROUP BY id




drop table tb