22,209
社区成员
发帖
与我相关
我的任务
分享
if OBJECT_ID('tb') is not null
drop table tb
go
create table tb(CusDsc varchar(10),PrdCd varchar(7),PrdNm varchar(10),SalePrice numeric(7,4))
insert into tb
select '北京朝批', 'A101600', 'CP 12P', 92.3077 union all
select '北京朝批', 'A101610', 'CP 6P' ,95.7265 union all
select '北京朝批', 'A101620', 'CP 2P' ,98.4615 union all
select '北京朝批', 'A101630', 'CP 20P', 159.2308 union all
select '北京朝批', 'A101660', 'CP 12P', 92.3077 union all
select '北京朝批', 'A101670', 'CP 6P', 95.7265 union all
select '北京朝批', 'A101680', 'CP 2P', 98.4615 union all
select '零售', 'A101600', 'CP 12P' ,100.2331 union all
select '零售', 'A101610', 'CP 6P' ,102.5641 union all
select '零售', 'A101620', 'CP 2P' ,68.3761 union all
select '零售', 'A101630', 'CP 20P' ,162.3932 union all
select '零售', 'A101660', 'CP 12P' ,100.2331
select CusDsc,PrdCd=STUFF((select ','+PrdCd from tb
where a.CusDsc=CusDsc and a.PrdNm=PrdNm and a.SalePrice=SalePrice for xml path('')),1,1,''),
PrdNm,SalePrice
from tb a
group by CusDsc,PrdNm,SalePrice
CusDsc PrdCd PrdNm SalePrice
北京朝批 A101600,A101660 CP 12P 92.3077
北京朝批 A101630 CP 20P 159.2308
北京朝批 A101620,A101680 CP 2P 98.4615
北京朝批 A101610,A101670 CP 6P 95.7265
零售 A101600,A101660 CP 12P 100.2331
零售 A101630 CP 20P 162.3932
零售 A101620 CP 2P 68.3761
零售 A101610 CP 6P 102.5641