100.行列转换

案例1-：

--行列转换示例

--测试数据
create table 表([11] varchar(2),[22] int,[33] int,[44] int,[55] int)
insert 表 select 'aa',1,2,3,6 
union all select 'bb',0,1,3,5 
union all select 'cc',1,2,3,6
union all select 'dd',1,2,3,6
union all select 'ee',1,2,3,6
go

--查询处理
declare @s1 varchar(8000),@s2 varchar(8000)
	,@s3 varchar(8000),@s4 varchar(8000),@s5 varchar(8000)
	,@i varchar(10)
select @s1='',@s2='',@s3='',@s4='',@s5='',@i='0'
select @s1=@s1+',@'+@i+' varchar(8000)'
	,@s2=@s2+',@'+@i+'='''''
	,@s3=@s3+'
select @'+@i+'=@'+@i+'+'',[''+[11]+'']=''+cast(['+name+'] as varchar) from 表'
	,@s4=@s4+',@'+@i+'=''select ''+substring(@'+@i+',2,8000)'
	,@s5=@s5+'+'' union all ''+@'+@i
	,@i=cast(@i as int)+1
from syscolumns 
where object_id('表')=id and colid<>1

select @s1=substring(@s1,2,8000)
	,@s2=substring(@s2,2,8000)
	,@s4=substring(@s4,2,8000)
	,@s5=substring(@s5,16,8000)

exec('declare '+@s1+'
select '+@s2+@s3+'
select '+@s4+'
exec('+@s5+')')
go

--删除测试表
drop table 表

/*--测试结果
aa          bb          cc          dd          ee          
----------- ----------- ----------- ----------- ----------- 
1           0           1           1           1
2           1           2           2           2
3           3           3           3           3
6           5           6           6           6

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

案例2：

--行列互换

/*--有表
indust     200301     200302     200303     
---------- ---------- ---------- ---------- 
a          111        222        333
b          444        555        666
c          777        888        999
d          789        910        012


--要求得到结果
日期     a    b    c    d    
------ ---- ---- ---- ---- 
200301 111  444  777  789
200302 222  555  888  910
200303 333  666  999  012
--*/

--创建测试表
create table test(indust varchar(10)
	,[200301] varchar(10)
	,[200302] varchar(10)
	,[200303] varchar(10))
insert test select 'a','111','222','333'
union all select 'b','444','555','666' 
union all select 'c','777','888','999'
union all select 'd','789','910','012'
go

--数据处理
declare @f1 varchar(8000),@f2 varchar(8000),@f3 varchar(8000)
select @f1='',@f2='',@f3=''
select @f1=@f1+',['+indust+']='''+[200301]+''''
	,@f2=@f2+','''+[200302]+''''
	,@f3=@f3+','''+[200303]+''''
from test
exec('select 日期=''200301'''+@f1
	+' union all select ''200302'''+@f2
	+' union all select ''200303'''+@f3)
go
--删除测试表
select * from test
drop table test

/*--测试结果
日期     a    b    c    d    
------ ---- ---- ---- ---- 
200301 111  444  777  789
200302 222  555  888  910
200303 333  666  999  012
--*/

案例3-生成字段名：

--处理示例

--示例数据
create table tb(A int,B int,C int,D int)
insert tb select 1,2 ,3 ,4   
union all select 5,6 ,7 ,8 
union all select 9,10,11,12
go

--查询处理
declare @s1 varchar(8000),@s2 varchar(8000)
	,@s3 varchar(8000),@s4 varchar(8000),@s5 varchar(8000)
	,@i varchar(10)
select @s1='',@s2='',@s3='',@s4='',@s5='',@i='0'
select @s1=@s1+',@'+@i+' varchar(8000)'
	,@s2=@s2+',@'+@i+'=''[字段1]='''''+name+''''''''
	,@s3=@s3+'
	,@'+@i+'=@'+@i+'+'',[字段''+cast(@i as varchar)+'']=''+cast(['+name+'] as varchar)'
	,@s4=@s4+',@'+@i+'=''select ''+@'+@i
	,@s5=@s5+'+'' union all ''+@'+@i
	,@i=cast(@i as int)+1
from syscolumns 
where object_id('tb')=id

select @s1=stuff(@s1,1,1,'')
	,@s2=stuff(@s2,1,1,'')
	,@s4=stuff(@s4,1,1,'')
	,@s5=stuff(@s5,1,15,'')

exec('declare '+@s1+',@i int
select '+@s2+'
set @i=1
select @i=@i+1'+@s3+'
from tb
select '+@s4+'
exec('+@s5+')')
go

--删除测试表
drop table tb

/*--测试结果

字段1  字段2         字段3         字段4         
---- ----------- ----------- ----------- 
A    1           5           9
B    2           6           10
C    3           7           11
D    4           8           12

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

案例4-字段名：

--行列转换示例1

--测试数据
create table 表(类别 varchar(10),男性 decimal(20,1),女性 decimal(20,1))
insert 表 select '小说',38.0,59.2
union all select '散文',18.9,30.6
union all select '哲学',16.2,10.2
go

--查询处理
declare @s1 varchar(8000),@s2 varchar(8000)
	,@s3 varchar(8000),@s4 varchar(8000),@s5 varchar(8000)
	,@i varchar(10)
select @s1='',@s2='',@s3='',@s4='',@s5='',@i='0'
select @s1=@s1+',@'+@i+' varchar(8000)'
	,@s2=@s2+',@'+@i+'=''性别='''''+name+''''''''
	,@s3=@s3+'
	,@'+@i+'=@'+@i+'+'',[''+[类别]+'']=''+cast(['+name+'] as varchar)'
	,@s4=@s4+',@'+@i+'=''select ''+@'+@i
	,@s5=@s5+'+'' union all ''+@'+@i
	,@i=cast(@i as int)+1
from syscolumns 
where object_id('表')=id and name<>'类别'

select @s1=stuff(@s1,1,1,'')
	,@s2=stuff(@s2,1,1,'')
	,@s3=stuff(@s3,1,4,'')
	,@s4=stuff(@s4,1,1,'')
	,@s5=stuff(@s5,1,15,'')

exec('declare '+@s1+'
select '+@s2+'
select '+@s3+'
from 表
select '+@s4+'
exec('+@s5+')')
go

--删除测试
drop table 表

/*--测试结果
性别   小说    散文    哲学    
---- ----- ----- ----- 
男性   38.0  18.9  16.2
女性   59.2  30.6  10.2

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