行列轉換(將表旋轉90度)
普通行列轉換
假設有張學生成績表(t)如下
Name Subject Result
張三 語文 73
張三 數學 83
張三 物理 93
李四 語文 74
李四 數學 84
李四 物理 94
想變成
姓名 語文 數學 物理
張三 73 83 93
李四 74 84 94
create table #t
(
Name varchar(10) ,
Subject varchar(10) ,
Result int
)
insert into #t(Name , Subject , Result) values('張三','語文','73')
insert into #t(Name , Subject , Result) values('張三','數學','83')
insert into #t(Name , Subject , Result) values('張三','物理','93')
insert into #t(Name , Subject , Result) values('李四','語文','74')
insert into #t(Name , Subject , Result) values('李四','數學','83')
insert into #t(Name , Subject , Result) values('李四','物理','93')
declare @sql varchar(8000)
set @sql = 'select Name as ' + '姓名'
select @sql = @sql + ' , sum(case Subject when ''' + Subject + ''' then Result end) [' + Subject + ']'
from (select distinct Subject from #t) as a
set @sql = @sql + ' from #t group by name'
exec(@sql)
drop table #t
--結果
姓名 數學 物理 語文
---------- ----------- ----------- -----------
李四 83 93 74
張三 83 93 73
如果上述兩表互相換一下：即
姓名 語文 數學 物理
張三 73 83 93
李四 74 84 94
想變成
Name Subject Result
張三 語文 73
張三 數學 83
張三 物理 93
李四 語文 74
李四 數學 84
李四 物理 94
create table #t
(
姓名 varchar(10) ,
語文 int ,
數學 int ,
物理 int
)
insert into #t(姓名 , 語文 , 數學 , 物理) values('張三',73,83,93)
insert into #t(姓名 , 語文 , 數學 , 物理) values('李四',74,84,94)
select 姓名 as Name,'語文' as Subject,語文 as Result from #t union
select 姓名 as Name,'數學' as Subject,數學 as Result from #t union
select 姓名 as Name,'物理' as Subject,物理 as Result from #t
order by 姓名 desc
drop table #t
Name Subject Result
---------- ------- -----------
張三 數學 83
張三 物理 93
張三 語文 73
李四 數學 84
李四 物理 94
李四 語文 74
(所影響的行數為 6 行)
將表旋轉90度.
將下表數據：
A b c d e
-------------------- ----------- ----------- ----------- -----------
x 1 2 3 4
y 5 6 7 8
z 9 10 11 12
轉化成如下結果：
a x y z
-------------------- ---------- ---------- ----------
b 1 5 9
c 2 6 10
d 3 7 11
e 4 8 12
--生成測試數據
create table test1(A varchar(20),b int,c int,d int,e int)
insert into test1 select 'x',1,2 ,3 ,4
insert into test1 select 'y',5,6 ,7 ,8
insert into test1 select 'z',9,10,11,12
--生成中間數據表
declare @s varchar(8000)
set @s='create table test2(a varchar(20)'
select @s=@s+','+A+' varchar(10)' from test1
set @s=@s+')'
exec(@s)
--借助中間表實現行列轉換
declare @name varchar(20)
declare t_cursor cursor for
select name from syscolumns
where id=object_id('test1') and colid>1 order by colid
open t_cursor
fetch next from t_cursor into @name
while @@fetch_status=0
begin
exec('select '+@name+' as t into test3 from test1')
set @s='insert into test2 select '''+@name+''''
select @s=@s+','''+rtrim(t)+'''' from test3
exec(@s)
exec('drop table test3')
fetch next from t_cursor into @name
end
close t_cursor
deallocate t_cursor
--查看行列互換處理結果
select * from test1
select * from test2
--刪除表
drop table test1
drop table test2
--生成測試數據
create table test1(姓名 varchar(10), 語文 int, 數學 int, 物理 int)
insert into test1 select '張三', 80, 90, 85
insert into test1 select '李四', 85, 92, 82
--生成中間數據表
declare @s varchar(8000)
set @s='create table test2(姓名 varchar(10)'
select @s=@s+','+ 姓名 +' varchar(10)' from test1
set @s=@s+')'
exec(@s)
--借助中間表實現行列轉換
declare @name varchar(20)
declare t_cursor cursor for
select name from syscolumns
where id=object_id('test1') and colid>1 order by colid
open t_cursor
fetch next from t_cursor into @name
while @@fetch_status=0
begin
exec('select '+@name+' as t into test3 from test1')
set @s='insert into test2 select '''+@name+''''
select @s=@s+','''+rtrim(t)+'''' from test3
exec(@s)
exec('drop table test3')
fetch next from t_cursor into @name
end
close t_cursor
deallocate t_cursor
--查看行列互換處理結果
select * from test1
select * from test2
--刪除表
drop table test1
drop table test2
/*result
姓名 語文 數學 物理
---------- ----------- ----------- -----------
張三 80 90 85
李四 85 92 82
（所影響的行數為 2 行）
姓名 張三 李四
---------- ---------- ----------
語文 80 85
數學 90 92
物理 85 82
（所影響的行數為 3 行）
*/
浙公網安備 33010602011771號