0

I have a scenario where my data is retrieved as given below

Product UOM Quantity

Petrol Barrels 13000

Petrol MT 20000

Diesel Barrels 7500

Diesel MT 2500

I need the output in a different output as given below

Product Barrels MT

Petrol 13000 20000

Diesel 7500 2500

Please help me in forming this query, I have been trying this for quite some time without successful result.

Rob van Wijk
  • 17,555
  • 5
  • 39
  • 55
Vasanth Kani
  • 1
  • Will there always be 3 columns? Or is that suppose to be dynamic? – sgeddes May 15 '14 at 13:54
  • Hi The columns will be three only, but there can be n no. of rows with different Product, different UOM – Vasanth Kani May 15 '14 at 13:58
  • @VasanthKani -- since you don't know the number of UOMs, you're going to need to use Dynamic SQL for that. There are plenty of examples on SO -- here is one: http://stackoverflow.com/questions/16978047/dynamic-oracle-pivot-in-clause – sgeddes May 15 '14 at 14:04

1 Answers1

1

It sounds like you're trying to PIVOT your results. Assuming you know the number of columns, one option is to use MAX with CASE:

select product, 
    max(case when uom = 'Barrels' then quantity end) BarrelsQty,
    max(case when uom = 'MT' then quantity end) MTQty
from yourtable
group by product
sgeddes
  • 62,311
  • 6
  • 61
  • 83