In table I have column ID, CAR_1,CAR_2,CAR_3...
SELECT 1 as ID, 100 as CAR_1, 200 as CAR_2, 300 as CAR_3 from dual;
How get CAR_DAY column eg. for CAR_1 = 1, CAR_2 = 2 etc..
How get this:
ID | CAR_ID | CAR_DAY |
---|---|---|
1 | 100 | 1 |
1 | 200 | 2 |
1 | 300 | 3 |
My demo but not working:
SELECT
ID,
CAR_ID,
CAR_DAY,
FROM
CARS
UNPIVOT
(
(CAR_ID, CAR_DAY) FOR COL IN
((CAR_1, 1), (CAR_2, 2), (CAR_3, 3))
)
Here how to do it using unpivot
:
SELECT ID, CAR_ID, REPLACE(CAR_DAY, 'CAR_', '') AS CAR_DAY
FROM (
SELECT 1 as ID, 100 as CAR_1, 200 as CAR_2, 300 as CAR_3
FROM dual
)
UNPIVOT
( CAR_ID FOR CAR_DAY IN (CAR_1, CAR_2, CAR_3) );