Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Convert few columns to rows in the select statement

I have a table like this :

-----------------------------------------------------------------
| ID |  ItemName | OldValue | newValue |  OrderId  | sequenceNo
-----------------------------------------------------------------
| 1 |  Item1   |   1     | 1.5     |  SO2   |   6
| 2 |  Item2   |   4     | 6       |  SO2   |   4
| 3 |  Item3   |   3     | 68      |  SO2    |  9 
------------------------------------------------------------------

I have to write a query where OldValue column data should come as new rows like example below

ItemName | allValues   |OrderId  | sequenceNo
----------------------------------------------
Item1   |   1          |  SO2   |   0
Item2   |   4          |  SO2   |   0
Item3   |   3          |  SO2   |   0
Item1   |   1.5        |  SO2   |   6
Item2   |   6          |  SO2   |   4
Item3   |   68         |  SO2   |   9
-----------------------------------------------

I did this using a UNION where I have written

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

select itemName , oldValue as allValues , OrderId from Orderdetails
UNION
select itemName , newValue as allValues , OrderId from Orderdetails

Is there a better way to write this without UNION.Any suggestions are highly appreciated.

>Solution :

You can use a VALUES clause to do the UNPIVOT

select t.itemname,
       u.allvalues,
       t.orderid,
       u.sequenceno
from the_table t
  cross join lateral (
     values (oldvalue, 0), (newvalue, t.sequenceno)
  ) as u(allvalues, sequenceno)
order by t.itemname;
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading