CODEWITHSUNDEEP
sqloracle-databaseunpivot
Pato
Pato

Reputation: 311

unpivot multiple columns in Oracle

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?

Upvotes: 0

Views: 32

Answers (2)

MT0
MT0

Reputation: 168806

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

Upvotes: 1

p3consulting
p3consulting

Reputation: 4680

select orderid, code, value from data 
unpivot (
    value FOR code IN (
                value1, value2
            )
)
;

(order being a reserved word changed to orderid)

Upvotes: 0

Related Questions