Sql Server Group by拼接列为字符串

DROP TABLE #tb
create table #tb(id int, value varchar(10),data VARCHAR(20)) 
insert into #tb values(1, '100','白车身') 
insert into #tb values(1, '200', '彩车身') 
insert into #tb values(2, '300','白车身') 
insert into #tb values(2, '400','彩车身') 
insert into #tb values(2, '500','装配') 
go 

select id, [values]=stuff((select  [data]+':'+[value]+' ' from #tb t where id=#tb.id for xml path('')), 1, 0, '') 
from #tb 
group by id 

select  [data]+':'+[value]+' ' from #tb t for xml path('')

--stuff函数介绍：stuff('abc',1,2,'def')  把'abc'字符串从第1个下标(a是下标1)开始，删除2位，然后拼接'def'   结果：'defc'

SELECT stuff('abc',1,2,'def')

顺带还学了两个函数，就很nice