Hi all,
I have a table saving values for every month in a year (columns like year_month) as it is displayed below
WITH TABLE_2017 AS (
SELECT 2 AS ID1,3 AS ID2,34.78 AS "201701",24.28 AS "201702",34.18 AS "201703",34.78 AS "201704",34.73 AS "201705",76.78 AS "201706",34.78 AS "201707",34.78 AS "201708",14.71 AS "201709",14.78 AS "201710",34.78 AS "201711",34.78 AS "201712" FROM DUAL
UNION ALL
SELECT 6 AS ID1,1 AS ID2,95.78 AS "201701",24.28 AS "201702",34.18 AS "201703",54.78 AS "201704",11.73 AS "201705",76.78 AS "201706",104.78 AS "201707",34.78 AS "201708",14.21 AS "201709",14.78 AS "201710",34.78 AS "201711",65.78 AS "201712" FROM DUAL
)
--,TABLE_2018 AS (
--SELECT 1 AS ID1,1 AS ID2,34.78 AS "201801",24.28 AS "201802",24.18 AS "201803",34.78 AS "201804",34.73 AS "201805",36.78 AS "201806",34.78 AS "201807",33.78 AS "201808",14.71 AS "201809",143.78 AS "201810",34.78 AS "201811",204.78 AS "201812" FROM DUAL
--UNION ALL
--SELECT 8 AS ID1,1 AS ID2,95.78 AS "201801",24.98 AS "201802",34.98 AS "201803",54.78 AS "201804",11.73 AS "201805",76.78 AS "201806",104.78 AS "201807",34.78 AS "201808",14.21 AS "201809",14.78 AS "201810",324.78 AS "201811",65.78 AS "201812" FROM DUAL
--)
SELECT ID1,
ID2,
YEAR_MONTH,
value
FROM (SELECT D.ID1,
D.ID2,
D."201701",
D."201702",
D."201703",
D."201704",
D."201705",
D."201706",
D."201707",
D."201708",
D."201709",
D."201710",
D."201711",
D."201712"
FROM TABLE_2017 D) P
UNPIVOT
(value FOR YEAR_MONTH IN ("201701",
"201702",
"201703",
"201704",
"201705",
"201706",
"201707",
"201708",
"201709",
"201710",
"201711",
"201712"));
How can I modify the above query to add also TABLE_2018 which contains data as above
with column_names "201801", ... "201812" and have per year_month
Could you help me please ?
Thanks in advance