How can I convert Row to Table Column in Oracle SQL Output?

Question

I want to convert my oracle sql output to table data row to Column.

My SQL output like

ITEM    | RESULT
-----------------
Shell   | 296.5
-----------------
Shell   | 299.8
-----------------
Shell   | 311
-----------------
Shell   | 289
-----------------
Lining  | 301.33
-----------------
Lining  | 296.5
-----------------
Lining  | 299
-----------------

But I want the output like -

Shell    |   Lining
----------------------
296.5    |  301.33
----------------------
299.8    |  296.5
----------------------
311      |  299
----------------------
289      |  0 -- 

If any item have less result then will be fillup with Zero(0)

So please suggest me the sql code that will fulfill my requirment. Thanks

Answer 1

You can do Pivot using row number so that you don't lose any value in your grouping while using max.

with cte as (
select 'Shell' as Item,   '296.5'  as Resultn from dual  union all 
select 'Shell' as Item,   '299.8'  as Resultn from dual  union all 
select 'Shell' as Item,   '311'    as Resultn from dual  union all 
select 'Shell' as Item,   '289'    as Resultn from dual  union all 
select 'Lining' as Item,  '301.33' as Resultn from dual  union all 
select 'Lining' as Item,  '296.5'  as Resultn from dual  union all 
select 'Lining' as Item,  '299'    as Resultn from dual   )

select nvl(Shell,0) Shell, nvl(Lining,0) Lining from (
select  c.item, c.Resultn, row_number() over (partition by Item order by Resultn) rownumn   
from cte c 
) z 
pivot 
(
max(Resultn) 
for item in ('Shell' as Shell  ,'Lining' as Lining ));

Output:

SHELL   LINING
289     296.5
296.5   299
311      0
299.8   301.33