想请问个问题.如何在SQL中,或者EXCEL中,把一个表的数据的行和列互换.就像C++中,把二元数组的[I][J]互换.希望高手指教.多谢了
------解决方案--------------------
--參考
http://community.csdn.net/Expert/topic/5259/5259689.xml?temp=.8638117
------解决方案--------------------
将下表数据：
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 @[email protected]+ ', '+A+ ' varchar(10) ' from test1
set @[email protected]+ ') '
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 '[email protected]+ ' as t into test3 from test1 ')
set @s= 'insert into test2 select ' ' '[email protected]+ ' ' ' '
select @[email protected]+ ', ' ' '+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