unpivot multiple columns in Oracle

Question

I have such table with 5 columns

order code1 value1 code2 value2

I want to to have one column for code and one column for value.

I can use union all select order,code1,value1 from table union all select order,code2,vakue2 from table

How can I fo it with unpivot clause?

Answer 1

You can use:

SELECT "order", code, value
FROM   table_name
       UNPIVOT (
         (code, value) FOR type IN (
           (code1, value1) AS 1,
           (code2, value2) AS 2
         )
       )

Which, for the sample data:

CREATE TABLE table_name ("order", code1, value1, code2, value2) AS
SELECT 1, 2, 3, 4,  5 FROM DUAL UNION ALL
SELECT 6, 7, 8, 9, 10 FROM DUAL;

Outputs:

order	CODE	VALUE
1	2	3
1	4	5
6	7	8
6	9	10

fiddle