关于sql查询的问题

select
  'name' as [id],
  case [id]
    when 1 then [name]
    else ''
  end as [1],
  case [id]
    when 2 then [name]
    else ''
  end as [2],
  case [id]
    when 3 then [name]
    else ''
  end as [3]
from [a]
union
select
  'password' as [id],
  case [id]
    when 1 then [password]
    else ''
  end as [1],
  case [id]
    when 2 then [password]
    else ''
  end as [2],
  case [id]
    when 3 then [password]
    else ''
  end as [3]
from [a]

create table f（id int,name varchar（10),password varchar（10))
insert into f select 1,'jack','123'
insert into f select 2,'lulu','456'
insert into f select 3,'jason','789'
create table #（col1 varchar（50),col2 varchar（50),colname varchar（50))

insert into # select * from （select id,row_number（) over（order by id) as px,'id' as colname  from f)tp
insert into # select * from （select name,row_number（) over（order by id) as px ,'name' as colname from f)tp
insert into # select * from （select password,row_number（) over（order by id) as px ,'password' as colname from f)tp

select 
max（case when colname='id' and col2='1' then col1 when colname='name' and col2='1' then col1 when colname='password' and col2='1' then col1 end) ,
max（case when colname='id' and col2='2' then col1 when colname='name' and col2='2' then col1 when colname='password' and col2='2' then col1 end),
max（case when colname='id' and col2='3' then col1 when colname='name' and col2='3' then col1 when colname='password' and col2='3' then col1 end)
from # group by colname

--旋转例子
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
go

--生成中间数据表
declare @s varchar（8000)
set @s = 'create table test2（a varchar（20)'
select @s = @s + ',' + a + ' varchar（10)' from test1
set @s = @s + ')'
ＥＸＥＣ（@s)
print @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
    ＥＸＥＣ（'select ' + @name + ' as t into test3 from test1')
    set @s='insert into test2 select ''' + @name + ''''
    select @s = @s + ',''' + rtrim（t) + '''' from test3
    ＥＸＥＣ（@s)
    ＥＸＥＣ（'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


------------------------------------------------
/*固定的写法:*/
select t1.* , t2.y , t3.z from
（select a = 'b' , x = b from test1 where a = 'x') t1, 
（select a = 'b' , y = b from test1 where a = 'y') t2,
（select a = 'b' , z = b from test1 where a = 'z') t3
where t1.a = t2.a and t1.a = t2.a
union all
select t1.* , t2.y , t3.z from
（select a = 'c' , x = c from test1 where a = 'x') t1, 
（select a = 'c' , y = c from test1 where a = 'y') t2,
（select a = 'c' , z = c from test1 where a = 'z') t3
where t1.a = t2.a and t1.a = t2.a
union all
select t1.* , t2.y , t3.z from
（select a = 'd' , x = d from test1 where a = 'x') t1, 
（select a = 'd' , y = d from test1 where a = 'y') t2,
（select a = 'd' , z = d from test1 where a = 'z') t3
where t1.a = t2.a and t1.a = t2.a
union all
select t1.* , t2.y , t3.z from
（select a = 'e' , x = e from test1 where a = 'x') t1, 
（select a = 'e' , y = e from test1 where a = 'y') t2,
（select a = 'e' , z = e from test1 where a = 'z') t3
where t1.a = t2.a and t1.a = t2.a