SQL SERVER 分组group by之后,字符串合并在一起,逗号隔开。
原本数据:
效果:
代码:
-use DB01;-- 建表create table dbo.tb_user_product(id int null,name varchar(10) null,product varchar(100) null ,amount decimal(9,2) null);-- 插入数据insert into dbo.tb_user_product values ('1001','大运','小米10至尊纪念版',5999);insert into dbo.tb_user_product values ('1001','大运','大米10好吃版',6800);insert into dbo.tb_user_product values ('1001','大运','华为30max',8999);insert into dbo.tb_user_product values ('1001','小星','华为10Pro',3500);insert into dbo.tb_user_product values ('1001','小星','OPPO9',2200);insert into dbo.tb_user_product values ('1001','阿飞','三星Note20',8500);-- 查询select * from dbo.tb_user_product; -- group by 字符串逗号隔开合并在一起select id,name,sum(amount) as amount,products = STUFF((select ','+product from dbo.tb_user_product bwhere a.id = b.id and a.name = b.name for xml path('')),1,1,'')from dbo.tb_user_product agroup by id,name;