[sql server]sql 行列互换

【www.quanqiunao.cn--游戏硬件】

 sql 行列互换
--行列互换

/*--有表
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
--*/

-------------------------------------------------------------------------------------

--行列转换示例

--测试数据
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 行）

codenames type flag
zhao a y
zhao b n
zhao c y
li a n
li b y
li c y
查询出来为

names typea flaga typeb falgb typec falgc
zhao a y b n c y
li a n b y c n

declare @sql varchar(8000)
set @sql="select names"
select @sql=@sql+",max(case when type="""+type+""" then type end) [type"+type+"]"
+",max(case when type="""+type+""" then flag end) [flag"+type+"]"
from tb group by type

exec (@sql+" from tb group by names")

本文来源：http://www.quanqiunao.cn/youxizixun/5573/