Oracle - add column in unpivot query which does not exist in the table

Question

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))
)

Answer 1

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) );

Demo here

Answer 2

Use AS in the UNPIVOT list:

SELECT ID,
       CAR_ID,
       CAR_DAY
FROM   CARS
UNPIVOT (
  CAR_ID FOR car_day IN (
    CAR_1 AS 1,
    CAR_2 AS 2,
    CAR_3 AS 3
  )
)

Which, for the sample data:

CREATE TABLE cars (id, car_1, car_2, car_3) AS
SELECT 1, 100, 200, 300 FROM DUAL;

Outputs:

ID	CAR_ID	CAR_DAY
1	100	1
1	200	2
1	300	3

fiddle