SQL中如何实现每个采购订单分别采购哪些物料的汇总表

环境：Oracle 11g

SELECT *
FROM (SELECT WLBM, CGDH, CGSL FROM TEST)
PIVOT(SUM(CGSL)
FOR CGDH IN('P001' P001,
'P002' P002,
'P003' P003,
'P004' P004)
)
ORDER BY WLBM
--------------------------------------------------------------------------------------

CREATE TABLE TEST (cgdh VARCHAR2(10),wlbm VARCHAR2(10),cgsl INT);

insert into test (cgdh,wlbm,cgsl) values('P001','A0027',20);
insert into test (cgdh,wlbm,cgsl) values('P001','A0018',80);
insert into test (cgdh,wlbm,cgsl) values('P001','A0018',300);
insert into test (cgdh,wlbm,cgsl) values('P001','A0024',20);
insert into test (cgdh,wlbm,cgsl) values('P001','A0019',80);
insert into test (cgdh,wlbm,cgsl) values('P001','A0034',40);
insert into test (cgdh,wlbm,cgsl) values('P001','A0026',20);
insert into test (cgdh,wlbm,cgsl) values('P001','A0009',650);
insert into test (cgdh,wlbm,cgsl) values('P001','A0004',560);
insert into test (cgdh,wlbm,cgsl) values('P002','A0026',20);
insert into test (cgdh,wlbm,cgsl) values('P002','A0009',650);
insert into test (cgdh,wlbm,cgsl) values('P002','A0004',560);
insert into test (cgdh,wlbm,cgsl) values('P002','A0015',240);
insert into test (cgdh,wlbm,cgsl) values('P002','A0002',90);
insert into test (cgdh,wlbm,cgsl) values('P002','A0005',1500);
insert into test (cgdh,wlbm,cgsl) values('P003','A0002',90);
insert into test (cgdh,wlbm,cgsl) values('P003','A0005',1500);
insert into test (cgdh,wlbm,cgsl) values('P003','A0007',20);
insert into test (cgdh,wlbm,cgsl) values('P003','A0005',80);
insert into test (cgdh,wlbm,cgsl) values('P003','A0006',60);
insert into test (cgdh,wlbm,cgsl) values('P003','A0008',20);
insert into test (cgdh,wlbm,cgsl) values('P003','A0036',100);
insert into test (cgdh,wlbm,cgsl) values('P004','A0026',20);
insert into test (cgdh,wlbm,cgsl) values('P004','A0009',650);
insert into test (cgdh,wlbm,cgsl) values('P004','A0004',560);
insert into test (cgdh,wlbm,cgsl) values('P004','A0015',240);
insert into test (cgdh,wlbm,cgsl) values('P004','A0002',90);
insert into test (cgdh,wlbm,cgsl) values('P004','A0005',1500);
insert into test (cgdh,wlbm,cgsl) values('P004','A0007',20);
insert into test (cgdh,wlbm,cgsl) values('P004','A0005',80);

语句：
select 物料编码, max(case 采购单号 when 'P001' then 采购数量 else 0 end ) P001,
max(case 采购单号 when 'P002' then 采购数量 else 0 end ) P002,
max(case 采购单号 when 'P003' then 采购数量 else 0 end ) P003,....... ----这个你有多少采购单号就加多少
from table1
group by 物料编码

declare @sql varchar(1000)
set @sql= 'select 物料编码, '
select @sql=@sql+',max (case 采购单价 when'''+采购单价+''' then 采购数量 else 0 end ) '''+采购单价+''''
from (select distinct 采购单价 from table)as a
set @sql=@sql+ 'from table group by 物料编号'
exec(@sql)

oracle可以使用列转行
select 采购单号,wmsys.wm_concat(物料编码||'数量:'||采购数量) from 订单表

用group分组查询