Posted on 2007-04-12 21:57
玄铁剑 阅读(363)
评论(0) 编辑 收藏 引用 所属分类:
sqlserver
以Nothwind DB为测试对象：
Declare @Sql varchar(8000)
Select @Sql='Select ShipName'
Select @Sql=@Sql+',Case when CustomerID='''+CustomerID+''' then EmployeeID else 0 end ['+CustomerID+']'
From (Select Distinct top 10 CustomerID From Orders) A
--Print @Sql
Select @Sql=@Sql+' From Orders'
Exec(@Sql)
declare @sql varchar(2000)
select @sql=isnull(@sql,'')+',['+rtrim(月份)+']=max(case 月份 when '+rtrim(月份)+ ' then rtrim(数据) else '''' end )'
from ta group by 月份
set @sql='select 员工'+@sql+' from ta group by 员工'
exec (@sql)
declare @sql varchar(2000)
select @sql=isnull(@sql,'')+',[月份'+rtrim(月份)+']=max(case 月份 when '+rtrim(月份)+ ' then rtrim(数据) else '''' end )'
from (select 1 as '月份'
union select 2
union select 3
union select 4
union select 5
union select 6
union select 7
union select 8
union select 9
union select 10
union select 11
union select 12)ta
set @sql='select 员工'+@sql+' from ta group by 员工'
exec (@sql)
select 员工,
[1月]=sum(case when 月份=1 then 数据 else 0 end),
[2月]=sum(case when 月份=2 then 数据 else 0 end),
[3月]=sum(case when 月份=3 then 数据 else 0 end),
[4月]=sum(case when 月份=4 then 数据 else 0 end),
[5月]=sum(case when 月份=5 then 数据 else 0 end),
[6月]=sum(case when 月份=6 then 数据 else 0 end),
[7月]=sum(case when 月份=7 then 数据 else 0 end),
[8月]=sum(case when 月份=8 then 数据 else 0 end),
[9月]=sum(case when 月份=9 then 数据 else 0 end),
[10月]=sum(case when 月份=10 then 数据 else 0 end),
[11月]=sum(case when 月份=11 then 数据 else 0 end),
[12月]=sum(case when 月份=12 then 数据 else 0 end)
from tbName
group by 员工
declare @m int,@sql varchar(1000)
set @m=0
while @m<12
begin
set @m=@m+1
select @sql=isnull(@sql,'')+',['+cast(@m as varchar)+'月]=sum(case when 月份='+cast(@m as varchar)+' then 数据 else 0 end)'
end
set @sql='select 员工'+@sql+' from ta group by 员工'
exec (@sql)