create table tb(id int, value 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
select id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '')
from tb
group by id
select id, [values]=stuff((select ','+[value] from (select 1 id,'a' value
union all
select 1 id,'b' value
union all
select 2 id,'a' value
union all
select 2 id,'b' value
union all
select 2 id,'c' value) t where id=a.id for xml path('')), 1, 1, '')
from (select 1 id,'a' value
union all
select 1 id,'b' value
union all
select 2 id,'a' value
union all
select 2 id,'b' value
union all
select 2 id,'c' value) a
group by id
2 个回复
天善_小龙 2016-04-22 回答
赞同来自: 梁勇 、seng 、alex_007
下面是个例子:
create table tb(id int, value 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
select id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '')
from tb
group by id
STUFF用法 (Transact-SQL): https://msdn.microsoft.com/zh-cn/library/ms188043.aspx
li052468 2016-04-22 回答
赞同来自:
union all
select 1 id,'b' value
union all
select 2 id,'a' value
union all
select 2 id,'b' value
union all
select 2 id,'c' value) t where id=a.id for xml path('')), 1, 1, '')
from (select 1 id,'a' value
union all
select 1 id,'b' value
union all
select 2 id,'a' value
union all
select 2 id,'b' value
union all
select 2 id,'c' value) a
group by id