34,575
社区成员
发帖
与我相关
我的任务
分享
id code
1 A001
1 A002
2 A003
3 A004
3 A005
id code
1 A001,A002
2 A003
3 A004,A005
with st(id,code) as (
select 1,'A001' union all
select 1,'A002' union all
select 2,'A003' union all
select 3,'A004' union all
select 3,'A005')
select id,code=stuff((select ','+code from st as a where a.id=st.id for xml path('')),1,1,'') from st group by id
/*
id code
--------------
1 A001,A002
2 A003
3 A004,A005
*/
-- 你自己把最后的 逗号去掉吧
create table test(id int, code varchar(10))
go
insert into test values
(1, 'A001'),(1, 'A002'),(2, 'A003'),(3, 'A004'),(3, 'A005')
go
select id ,
(select code + ',' from test where id = t.id for xml path('') ) code
from test t
group by id
go
drop table test
go
(5 行受影响)
id code
----------- --------------------------
1 A001,A002,
2 A003,
3 A004,A005,
(3 行受影响)