Oracle:将列转置为行

Question

我有以下表格中的数据

  bins  |   pro
  -------------
    1   |   0.10
    2   |   0.05
    3   |   0.78
    4   |   0.20 
    5   |   0.82
    6   |   0.45

我需要一个包含以下结果的查询：

   1    |   2   |   3   |   4   |   5   |   6
-------------------------------------------------
  0.10  | 0.05  |  0.78 |  0.20 |  0.82 |  0.45

Answer 1

对于固定的bin列表，您可以进行条件聚合：

select 
    max(case when bins = 1 then pro end) as bins1,
    max(case when bins = 2 then pro end) as bins2
    ...
    max(case when bins = 6 then pro end) as bins6
from mytable

Answer 2

或者，使用pivot本身：

SQL> with data (bins, pro) as
  2  -- sample data
  3    (select 1, 0.1  from dual union all
  4     select 2, 0.05 from dual union all
  5     select 3, 0.78 from dual union all
  6     select 4, 0.2  from dual union all
  7     select 5, 0.82 from dual union all
  8     select 6, 0.45 from dual
  9    )
 10  -- pivoting begins here
 11  select *
 12  from (select bins, pro
 13        from data
 14       )
 15  pivot
 16    (max(pro)
 17     for bins in (1, 2, 3, 4, 5, 6)
 18    )
 19  ;

    1     2     3     4     5     6
----- ----- ----- ----- ----- -----
 0.10  0.05  0.78  0.20  0.82  0.45

SQL>