ORACLE 行列转换

Question

要求 将下列表机构
    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期望各位达人能给个合理的答案。先在此跪谢了

Answer 1

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楼主试试，是否满足要求啊？
 

Answer 2

www.baidu.comoracle 行列转换