将数据分类显示成二维表的形式

来源：互联网发布：为什么安装Linux虚拟机编辑：程序博客网时间：2024/05/16 07:43

--創建數據庫
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')