--創建數據庫
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([code] varchar(6),[Month] int,[Num] int)
--插入值
insert [tb]
select 'C00001',200401,3 union all
select 'C00001',200402,1 union all
select 'C00001',200403,1 union all
select 'C00001',200404,3 union all
select 'C00001',200405,3 union all
select 'C00001',200604,1 union all
select 'C00002',200401,3 union all
select 'C00002',200402,2 union all
select 'C00002',200404,1 union all
select 'C00002',200405,1 union all
select 'C99999',200401,5 union all
select 'C99999',200402,2 union all
select 'C99999',200403,2
go
---查询---
declare @sql varchar(8000)
select
@sql=isnull(@sql+',','')
+'sum(case when [month]='+ltrim([month])+' then num else 0 end) as ['+ltrim([month])+']'
from
(select distinct [month] from tb) t
print @sql
exec ('select code,'+@sql+' from tb group by code')
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([code] varchar(6),[Month] int,[Num] int)
--插入值
insert [tb]
select 'C00001',200401,3 union all
select 'C00001',200402,1 union all
select 'C00001',200403,1 union all
select 'C00001',200404,3 union all
select 'C00001',200405,3 union all
select 'C00001',200604,1 union all
select 'C00002',200401,3 union all
select 'C00002',200402,2 union all
select 'C00002',200404,1 union all
select 'C00002',200405,1 union all
select 'C99999',200401,5 union all
select 'C99999',200402,2 union all
select 'C99999',200403,2
go
---查询---
declare @sql varchar(8000)
select
@sql=isnull(@sql+',','')
+'sum(case when [month]='+ltrim([month])+' then num else 0 end) as ['+ltrim([month])+']'
from
(select distinct [month] from tb) t
print @sql
exec ('select code,'+@sql+' from tb group by code')