Convert few columns to rows in the select statement-CodePudding

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

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.

CodePudding user response：

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;