行列互换的复杂交叉表.sql

sqlserver 数据库编程的绝好脚本

--创建测试数据
create table 表(年 int,项目 varchar(10)
	,平均值 decimal(20,1)
	,最大值 decimal(20,1)
	,最小值 decimal(20,1)
	,超标率 decimal(20,1)
)
insert into 表
select 2001,'项目1',1.5,2.1,1.1,0.1
union all select 2001,'项目2',2.0,5.0,1.5,5.5
union all select 2003,'项目10',2.1,5.2,2.5,5.5
go

--处理
declare @s varchar(8000),@s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000)

--处理项目
set @s=''
select @s=@s+',['+项目+']=max(case 项目 when '''''+项目+''''' then 值 end)'
from(select distinct 项目,id=cast(right(项目,len(项目)-2) as int) from 表
) a order by id

--处理字段(指标)
select @s1='',@s2='',@s3=''
select @s1=@s1+',@'+id+' varchar(8000)'
	,@s2=@s2+'
set @'+id+'=''select 年,项目,id='+id+
	',内容='''''+name+''''',值='+name+' from 表'''
	,@s3=@s3+'+'' union all ''+@'+id
from(
select name,id=cast(colid as varchar),colid
from syscolumns where object_id('表')=id
	and name not in('年','项目')
) a order by colid

select @s1=substring(@s1,2,8000),@s3=substring(@s3,16,8000)
exec('declare '+@s1+'
'+@s2+'
exec(''select 年,内容'+@s+' from(''+'
+@s3+'+'') a group by 年,id,内容 order by 年,id'')')
go

--删除测试数据
drop table 表

/*--测试结果

年           内容     项目1                    项目2                    项目10                   
----------- ------ ---------------------- ---------------------- ---------------------- 
2001        平均值    1.5                    2.0                    NULL
2001        最大值    2.1                    5.0                    NULL
2001        最小值    1.1                    1.5                    NULL
2001        超标率    .1                     5.5                    NULL
2003        平均值    NULL                   NULL                   2.1
2003        最大值    NULL                   NULL                   5.2
2003        最小值    NULL                   NULL                   2.5
2003        超标率    NULL                   NULL                   5.5

（所影响的行数为 8 行）
--*/