select 货位,sum(case when 产品编号='电脑' then 数量 else 0 end )as 电脑 sum(case when 产品编号='打印机' then 数量 else 0 end )as 打印机 sum(case when 产品编号='财务软件' then 数量 else 0 end )as 财务软件 from 表 group by 货位
在 SQL Server 2005里有实现交叉报表 的关键字
Create table test (addr nvarchar(10),pid nvarchar(10),amt int) go insert test values(N'郑州',N'电脑',10) insert test values(N'郑州',N'打印机',15) insert test values(N'洛阳',N'打印机',20) insert test values(N'新乡',N'财务软件',10)select * from testdeclare @sql nvarchar(400) set @sql = 'select addr' select @sql = @sql + ',sum(case pid when N'''+pid+''' then amt end) ['+pid+']' from (select distinct pid from test) as a select @sql = @sql+' from test group by addr' print(@sql) exec(@sql)
sum(case when 产品编号='打印机' then 数量 else 0 end )as 打印机
sum(case when 产品编号='财务软件' then 数量 else 0 end )as 财务软件
from 表
group by 货位
go
insert test values(N'郑州',N'电脑',10)
insert test values(N'郑州',N'打印机',15)
insert test values(N'洛阳',N'打印机',20)
insert test values(N'新乡',N'财务软件',10)select *
from testdeclare @sql nvarchar(400)
set @sql = 'select addr'
select @sql = @sql + ',sum(case pid when N'''+pid+''' then amt end) ['+pid+']'
from (select distinct pid from test) as a
select @sql = @sql+' from test group by addr'
print(@sql)
exec(@sql)