declare @sql varchar(8000) set @sql='' select @sql=@sql+',['+rtrim([month])+']=sum(case [month] when '+rtrim([month])+') then je else 0 end' from 表 group by [month] order by [month] set @sql='select [year]'+@sql+' from 表 group by [year]' exec(@sql)
declare @sql varchar(8000) set @sql='' select @sql=@sql+',['+rtrim([month])+']=sum(case [month] when '+rtrim([month])+' then je else 0 end)' from 表 group by [month] order by [month] set @sql='select [year]'+@sql+' from 表 group by [year]' exec(@sql)
-- 2005有直接的语法支持SELECT * FROM tb PIVOT( SUM(je) FOR [Month] IN([1], [2], [3], [4]) )p
--2005 try select * from @t pivot ( max(je) for month in ([1],[2],[3],[4]) ) as pivt
pivot和unpivot还是很弱, 复杂一点的处理不了. :( 并不太看好
create table testcsdn ( [year] int, [month] int, [je] int ) insert into testcsdn select 2006,1,500 union all select 2006,2,600 union all select 2006,3,700 union all select 2006,4,800select * from testcsdn declare @sql varchar(800) set @sql='' select @sql=@sql+',sum(case [month] when '+rtrim([month])+' then je else 0 end) as ['+rtrim([month])+']' from testcsdn group by [month] set @sql='select [year]'+@sql+' from testcsdn group by [year]' exec(@sql)新手,熟悉一下 :)
set @sql=''
select @sql=@sql+',['+rtrim([month])+']=sum(case [month] when '+rtrim([month])+') then je else 0 end' from 表 group by [month] order by [month]
set @sql='select [year]'+@sql+' from 表 group by [year]'
exec(@sql)
set @sql=''
select @sql=@sql+',['+rtrim([month])+']=sum(case [month] when '+rtrim([month])+' then je else 0 end)' from 表 group by [month] order by [month]
set @sql='select [year]'+@sql+' from 表 group by [year]'
exec(@sql)
FROM tb
PIVOT(
SUM(je)
FOR [Month] IN([1], [2], [3], [4])
)p
select * from @t
pivot
( max(je)
for month in ([1],[2],[3],[4])
) as pivt
并不太看好
(
[year] int,
[month] int,
[je] int
)
insert into testcsdn
select 2006,1,500 union all
select 2006,2,600 union all
select 2006,3,700 union all
select 2006,4,800select * from testcsdn
declare @sql varchar(800)
set @sql=''
select @sql=@sql+',sum(case [month] when '+rtrim([month])+' then je else 0 end) as ['+rtrim([month])+']'
from testcsdn group by [month]
set @sql='select [year]'+@sql+' from testcsdn group by [year]'
exec(@sql)新手,熟悉一下 :)