sql 行列互换

 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')