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?
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 |
select orderid, code, value from data
unpivot (
value FOR code IN (
value1, value2
)
)
;
(order being a reserved word changed to orderid)
