How to swap the rows and columns of a table's data

66381397

I would like to ask a question. How to swap the rows and columns of a table's data in SQL, or EXCEL. Just like in C ++, swap [I] [J] of a binary array. I hope expert advice. Thank you Up

我是杀人狂

The following table data:
A b c d e
-------------------- ----------- ----------- -------- --- -----------
x 1 2 3 4
y 5 6 7 8
z 9 10 11 12

Translates to the following result:
a x y z
-------------------- ---------- ---------- ----------
b 1 5 9
c 2 6 10
d 3 7 11
e 4 8 12

-Generate test data
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


-Generate intermediate data tables
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)

--Realize row and column conversion with the help of intermediate tables
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


--View row and column swap processing results
select * from test1
select * from test2

--Delete the table
drop table test1
drop table test2

66381397

Thank you so much.