SQL server 数据库中，如何将分组后元素用逗号隔开并作为一条记录？

发布网友发布时间：2022-05-03 16:19

共2个回答

热心网友时间：2022-05-03 17:48

创建表及数据

create table t1
(mid int,
uid varchar(1))
insert into t1 values (1,'a')
insert into t1 values (1,'b')
insert into t1 values (1,'b')
insert into t1 values (1,'c')
insert into t1 values (1,'d')
insert into t1 values (2,'a')
insert into t1 values (2,'b')
insert into t1 values (2,'c')
insert into t1 values (2,'c')
insert into t1 values (3,'a')
insert into t1 values (3,'b')
insert into t1 values (3,'c')
insert into t1 values (3,'c')

执行

select mid, items=stuff((select ','+uid from (select distinct mid,uid from t1) t where mid=s.mid for xml path('')), 1, 1, '')
from (select distinct mid,uid from t1) s
group by mid

结果

热心网友时间：2022-05-03 19:06

sqlserver2005以上版本
select mid,stuff((select distinct ','+uid from t1 where a.mid=mid for xml path('')),1,1,'')items
from t1 a group by mid