create table tv(YYYYMM varchar(20),personcount int) insert tv select '201004',50 union all select '201005',80 union all select '201006',90 union all select '201007',180 union all select '201008',380 union all select '201009',580 union all select '201010',82 union all select '201011',72 godeclare @sql varchar(1000) set @sql='' select @sql=@sql+',['+yyyymm+']=max(case yyyymm when '''+yyyymm+''' then personcount else null end)' from tv set @sql='select '+stuff(@sql,1,1,'')+' from tv' exec(@sql)
确实好方法: declare @sql varchar(1000) set @sql='' select @sql=@sql+',['+yyyymm+']=max(case yyyymm when '''+yyyymm+''' then personcount else null end)' from tv set @sql='select '+stuff(@sql,1,1,'')+' from tv' exec(@sql)
select * from @a s pivot (sum(personcount) for YYYYMM in ([201004],[201005],[201006],[201007],[201008],[201009],[201010],[201011]) --'+@sql+' ) as P
--Step1 select * into #tmp from @a --Step2 declare @sql varchar(max), @sqlAll nvarchar(max) select @sql='' select @sql=@sql+'['+YYYYMM+'],' from @a select @sql=left(@sql,len(@sql)-1) print @sql --Step3 select @sqlAll=' select * from #tmp s pivot (max(personcount) for YYYYMM in ('+@sql+')) as p' -- --Step4 exec sp_executesql @sqlAll drop table #tmp
declare @a table (YYYYMM varchar(20),personcount int)insert @a select '201004',50 union all select '201005',80 union all select '201006',90 union all select '201007',180 union all select '201008',380 union all select '201009',580 union all select '201010',82 union all select '201011',72--Step1 select * into #tmp from @a --Step2 declare @sql varchar(max), @sqlAll nvarchar(max) select @sql='' select @sql=@sql+'['+YYYYMM+'],' from @a select @sql=left(@sql,len(@sql)-1) print @sql --Step3 select @sqlAll=' select * from #tmp s pivot (max(personcount) for YYYYMM in ('+@sql+')) as p' -- --Step4 exec sp_executesql @sqlAll drop table #tmp sql2005 sql2008直接运行
select '201004'=sum(case yyyymm when '201004' then personcount else 0 end), '201005'=sum(case yyyymm when '201005' then personcount else 0 end), '201006'=sum(case yyyymm when '201006' then personcount else 0 end), '201007'=sum(case yyyymm when '201007' then personcount else 0 end), '201008'=sum(case yyyymm when '201008' then personcount else 0 end), '201009'=sum(case yyyymm when '201009' then personcount else 0 end), '2010010'=sum(case yyyymm when '201010' then personcount else 0 end), '2010011'=sum(case yyyymm when '201011' then personcount else 0 end)from tb
http://blog.csdn.net/xys_777/archive/2010/06/22/5685953.aspx
insert tv
select '201004',50
union all
select '201005',80
union all
select '201006',90
union all
select '201007',180
union all
select '201008',380
union all
select '201009',580
union all
select '201010',82
union all
select '201011',72
godeclare @sql varchar(1000)
set @sql=''
select @sql=@sql+',['+yyyymm+']=max(case yyyymm when '''+yyyymm+''' then personcount else null end)'
from tv
set @sql='select '+stuff(@sql,1,1,'')+' from tv'
exec(@sql)
确实好方法:
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+',['+yyyymm+']=max(case yyyymm when '''+yyyymm+''' then personcount else null end)'
from tv
set @sql='select '+stuff(@sql,1,1,'')+' from tv'
exec(@sql)
pivot
(sum(personcount)
for YYYYMM in ([201004],[201005],[201006],[201007],[201008],[201009],[201010],[201011]) --'+@sql+'
) as P
--Step1
select * into #tmp from @a
--Step2
declare @sql varchar(max), @sqlAll nvarchar(max)
select @sql=''
select @sql=@sql+'['+YYYYMM+'],'
from @a
select @sql=left(@sql,len(@sql)-1)
print @sql
--Step3
select @sqlAll='
select * from #tmp s
pivot
(max(personcount)
for YYYYMM in ('+@sql+')) as p' --
--Step4
exec sp_executesql @sqlAll
drop table #tmp
declare @a table (YYYYMM varchar(20),personcount int)insert @a
select '201004',50
union all
select '201005',80
union all
select '201006',90
union all
select '201007',180
union all
select '201008',380
union all
select '201009',580
union all
select '201010',82
union all
select '201011',72--Step1
select * into #tmp from @a
--Step2
declare @sql varchar(max), @sqlAll nvarchar(max)
select @sql=''
select @sql=@sql+'['+YYYYMM+'],'
from @a
select @sql=left(@sql,len(@sql)-1)
print @sql
--Step3
select @sqlAll='
select * from #tmp s
pivot
(max(personcount)
for YYYYMM in ('+@sql+')) as p' --
--Step4
exec sp_executesql @sqlAll
drop table #tmp
sql2005 sql2008直接运行
'201005'=sum(case yyyymm when '201005' then personcount else 0 end),
'201006'=sum(case yyyymm when '201006' then personcount else 0 end),
'201007'=sum(case yyyymm when '201007' then personcount else 0 end),
'201008'=sum(case yyyymm when '201008' then personcount else 0 end),
'201009'=sum(case yyyymm when '201009' then personcount else 0 end),
'2010010'=sum(case yyyymm when '201010' then personcount else 0 end),
'2010011'=sum(case yyyymm when '201011' then personcount else 0 end)from tb