关于sql查询的问题

[「已注销」]

数据库中有table A数据如下:
id name password
1 jack 123
2 lulu 456
3 jason 789
我要用个什么样的查询语句来实现查询的结果如下所示：
id 1 2 3
name jack lulu jason
password 123 456 789
请高手指教

[wzy_love_sly]

这题目和普通行转列不一样，普通行转列简单，这可以说是旋转式行转列，PIVOT/UNPIVOT很难实现

[wdtclv]

select * from A
order by id , name, password

[cnming]

加入Case

[wuyi8808]

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]

[wuyi8808]

select [id],max([1]) as [1],max([2]) as [2],max([3]) as [3]
from
(
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]
) [B]
group by [id]
union
select [id],max([1]) as [1],max([2]) as [2],max([3]) as [3]
from
(
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]
) [B]
group by [id]

[wzy_love_sly]

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

(无列名) (无列名) (无列名)
1 2 3
jack lulu jason
123 456 789

[wzy_love_sly]

--旋转例子
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 + ')'
exec(@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
    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


------------------------------------------------
/*固定的写法:*/
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

a x y z
b 1 5 9
c 2 6 10
d 3 7 11
e 4 8 12

[QQQQAnnie]

case then是对的

[chuxue1342]

这种需要的帖发了不只十次!如果你用的是sql server2005告诉你一个简单的办法!
SQL Server 2005之PIVOT/UNPIVOT行列转换
发表于：2007年12月25日 14时14分30秒阅读(0)评论(1)本文链接：http://user.qzone.qq.com/113620916/blog/1198563270

SQL Server 2005之PIVOT/UNPIVOT行列转换
SQL Server2005引入了很多迎合开发者口味的新特性，虽然改动不大，却大大了减少了开发者的工作量，这种替用户考虑的开发思路，值得称赞。
在SQL Server2000中，要实现行列转换，需要综合利用聚合函数和动态SQL，实现起来需要一定的技巧，所以在CSDN的SQL讨论区里可以看到大量询问行列转换如何实现的问题。到了2005中，使用新引进的关键字PIVOT/UNPIVOT，可以轻松实现行列转换的需求。
好像Oracle11g也准备引入PIVOT/UNPIVOT特性，对于Oracle开发来说，It's a good news。
本文通过两个简单的例子展示PIVOT/UNPIVOT的用法。详细的语法请参考联机帮助。

PIVOT
创建测试表，插入测试数据
create table test(id int,name varchar(20),quarter int,profile int)
insert into test values(1,'a',1,1000)
insert into test values(1,'a',2,2000)
insert into test values(1,'a',3,4000)
insert into test values(1,'a',4,5000)
insert into test values(2,'b',1,3000)
insert into test values(2,'b',2,3500)
insert into test values(2,'b',3,4200)
insert into test values(2,'b',4,5500)
select * from test
id name quarter profile
----------- -------------------- ----------- -----------
1 a 1 1000
1 a 2 2000
1 a 3 4000
1 a 4 5000
2 b 1 3000
2 b 2 3500
2 b 3 4200
2 b 4 5500
(8 row(s) affected)
利用PIVOT将个季度的利润转成横向显示：
select id,name,
[1] as "一季度",
[2] as "二季度",
[3] as "三季度",
[4] as "四季度"
from
test
pivot
(
sum(profile)
for quarter in
([1],[2],[3],[4])
)
as pvt
id name 一季度 二季度 三季度 四季度
----------- -------------------- ----------- ----------- ----------- -----------
1 a 1000 2000 4000 5000
2 b 3000 3500 4200 5500
(2 row(s) affected)
UNPIVOT
建立测试表，插入测试数据
drop table test
create table test(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int)
insert into test values(1,'a',1000,2000,4000,5000)
insert into test values(2,'b',3000,3500,4200,5500)

select * from test
id name Q1 Q2 Q3 Q4
----------- -------------------- ----------- ----------- ----------- -----------
1 a 1000 2000 4000 5000
2 b 3000 3500 4200 5500
(2 row(s) affected)
利用UNPIVOT，将同一行中四个季度的列数据转换成四行数据：
select id,name,quarter,profile
from
test
unpivot
(
profile
for quarter in
([Q1],[Q2],[Q3],[Q4])
)
as unpvt
id name quarter profile
----------- -------------------- ---------- -----------
1 a Q1 1000
1 a Q2 2000
1 a Q3 4000
1 a Q4 5000
2 b Q1 3000
2 b Q2 3500
2 b Q3 4200
2 b Q4 5500
(8 row(s) affected)

[cnming]

PIVOT/UNPIVOT 不错，学习了