sql server 多行纪录合并为一行

create table #table
(
    id int,
    color nvarchar(max)
)

insert into #table(id, color) values (1, '红')
insert into #table(id, color) values (1, '黄')
insert into #table(id, color) values (1, '蓝')
insert into #table(id, color) values (1, '绿')

select id, color=(STUFF((select ',' + color from #table where id = a.id for xml path('')),1,1,'')) into #colors from #table a group by id

select * from #colors

drop table #table
drop table #colors

 结果集：