declare @s varchar(8000)
set @s='select 采购单位'
select @s=@s+',['+类别+']=sum(case 类别 when '''+类别+''' then 数量 else 0 end)'
from 表 group by 类别 order by 类别
set @s=' from 表 group by 采购单位 order by 采购单位'
exec(@s)
set @s='select 采购单位'
select @s=@s+',['+类别+']=sum(case 类别 when '''+类别+''' then 数量 else 0 end)'
from 表 group by 类别 order by 类别
set @s=' from 表 group by 采购单位 order by 采购单位'
exec(@s)
insert into t select 'cpu ',1500,2 ,'电脑类','科技大厦'
insert into t select '内存条',100 ,1 ,'电脑类','电脑公司'
insert into t select 'cpu ',1000,1 ,'电脑类','科技大厦'
insert into t select '油笔 ',5 ,5 ,'日常类','日用品公司'
insert into t select '电脑书',45 ,10,'书籍类','书店'
insert into t select '笔记本',100 ,1 ,'日常类','电脑公司'
declare @s varchar(8000)
set @s='select 采购单位'
select @s=@s+',['+类别+']=sum(case 类别 when '''+类别+''' then 单价 else 0 end)'
from t group by 类别 order by 类别
set @s=@s+' from t group by 采购单位 order by 采购单位'
exec(@s)/*采购单位 电脑类 日常类 书籍类
--------- -------- -------- --------
电脑公司 100 100 0
科技大厦 2500 0 0
日用品公司 0 5 0
书店 0 0 45
*/drop table T
--测试数据
create table scores(货物ID int,供应商 varchar(10),采购数量 int)
go
insert into scores
select 1,'AAA',10 union
select 1,'BBB',30 union
select 2,'AAA',15 union
select 2,'BBB',40 union
select 2,'CCC',60 union
select 2,'DDD',70go
declare @s varchar(8000)
set @s=''
select @s=@s+',[供应商'+dt+']=sum(case 供应商 when '''+dt+''' then 采购数量 else 0 end)'
from(select distinct dt= 供应商 from scores)aexec('select 货物id'+@s+' from scores group by 货物id')
go--删除测试
drop table scores/*货物id 供应商AAA 供应商BBB 供应商CCC 供应商DDD
----------- ----------- ----------- ----------- -----------
1 10 30 0 0
2 15 40 60 70*/