有表TableA:
col1 col2 col3
A x 1
A y 2
A z 3
B x 2
B y 1
B z 1是否可以把数据显示成(用一个SQL):
x y z
A 1 2 3
B 2 1 1如果col1,col2的值是变化,显示列是否动态变化?
col1 col2 col3
A x 1
A y 2
A z 3
B x 2
B y 1
B z 1是否可以把数据显示成(用一个SQL):
x y z
A 1 2 3
B 2 1 1如果col1,col2的值是变化,显示列是否动态变化?
SQL> select tt.col1,
2 max(decode(col2,'x',col3)) as x,
3 max(decode(col2,'y',col3)) as y,
4 max(decode(col2,'z',col3)) as z
5 from tablename tt
6 group by tt.col1;COL1 X Y Z
---- ---------- ---------- ----------
A 1 2 3
B 2 1 12:如果列是不固定的,参考下面贴子中的解决方案,用存储过程实现的:http://topic.csdn.net/u/20071225/22/32c53bad-f6f5-47d2-90ea-16c0daa8eefb.htmlTRY IT ..
2 union all
3 select 'a' con1,'y' col2,2 col3 from dual
4 union all
5 select 'a' con1,'z' col2,3 col3 from dual
6 union all
7 select 'b' con1,'x' col2,2 col3 from dual
8 union all
9 select 'b' con1,'y' col2,1 col3 from dual
10 union all
11 select 'b' con1,'z' col2,1 col3 from dual
12 )
13 select con1,max(decode(rn,1,col3,null)) x,
14 max(decode(rn,2,col3,null)) y,
15 max(decode(rn,3,col3,null)) z
16 from (select a.*,row_number()over(partition by con1 order by col2) rn from a)
17 group by con1
18 /
CON1 X Y Z
---- ---------- ---------- ----------
a 1 2 3
b 2 1 1
sum(decode(col2,'x',col3)) as x,
sum(decode(col2,'y',col3)) as y,
sum(decode(col2,'z',col3)) as z
from TableA
group by col1
2 max(decode(col2,'x',col3)) as x,
3 max(decode(col2,'y',col3)) as y,
4 max(decode(col2,'z',col3)) as z
5 from tablename tt
6 group by tt.col1;