I have the data in the following tables
bins | pro
-------------
1 | 0.10
2 | 0.05
3 | 0.78
4 | 0.20
5 | 0.82
6 | 0.45
I need a query with this result:
1 | 2 | 3 | 4 | 5 | 6
-------------------------------------------------
0.10 | 0.05 | 0.78 | 0.20 | 0.82 | 0.45
For a fixed list of bins, you can do conditional aggregation:
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
Or, using pivot itself:
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>
