ORACLE 行列转换

[wenpan007hot]

要求 将下列表机构
id date a b c
1 2009-06-20 10:00:00 0.99 0.88 0.77
1 2009-06-20 11:00:00 0.89 0.78 0.37
1 2009-06-20 12:00:00 0.79 0.98 0.77

转化为
id clock_10_a clock_10_b clock_10_c clock_11_a clock_11_b clock_11_c ....
1 0.99 0.88 0.77 0.89 0.78 0.37



期望各位达人能给个合理的答案。先在此跪谢了

[jdsnhan]

www.baidu.com

oracle 行列转换

[wenpan007hot]

谢谢1楼的答案。我试试。如有什么问题。希望不要吝啬赐教

[cab1225]

[Quote=引用 1 楼 dxmt0 的回复:]
select id,max(decode(rn,1,a,null)) clock_10_a,
max(decode(rn,1,b,null)) clock_10_b,
max(decode(rn,1,c,null)) clock_10_c,
max(decode(rn,2,a,null)) clock_11_a,
max(decode(rn,2,b,null)) clock_11_b,
max(decode(rn,2,c,null)) clock_11_c,
max(decode(rn,3,a,null)) clock_12_a,
max(decode(rn,3,b,null)) clock_12_b,
…
[/Quote]

顶一下！！这个方法应该是对的。。。楼主试好给个答复哦！

[dxmt0]

select id,max(decode(rn,1,a,null)) clock_10_a,
max(decode(rn,1,b,null)) clock_10_b,
max(decode(rn,1,c,null)) clock_10_c,
max(decode(rn,2,a,null)) clock_11_a,
max(decode(rn,2,b,null)) clock_11_b,
max(decode(rn,2,c,null)) clock_11_c,
max(decode(rn,3,a,null)) clock_12_a,
max(decode(rn,3,b,null)) clock_12_b,
max(decode(rn,3,c,null)) clock_12_c
from (select id,v_date,a,b,c,row_number()over(partition by id order by id,v_date,a,b) rn
from table_name)
group by id
order by id;

输出是：ID CLOCK_10_A CLOCK_10_B CLOCK_10_C CLOCK_11_A CLOCK_11_B CLOCK_11_C CLOCK_12_A CLOCK_12_B CLOCK_12_C
1 0.99 0.88 0.77 0.89 0.78 0.37 0.79 0.98 0.77



楼主试试，是否满足要求啊？