Inbuilt functions strtok_split_to_table & regexp_split_to_table does not support splitting multiple columns. Custom query need to be created to get the required result.
Lets create a volatile table & populated data for illustration purpose.
CREATE VOLATILE TABLE Multicolum_split
(
Id INTEGER,
week_start_dt DATE,
days VARCHAR(300),
hours_of_work VARCHAR(300)
)
ON COMMIT PRESERVE ROWS;
INSERT INTO Multicolum_split VALUES(1,'2020-08-31','Sun,Mon,Tue,Wed,Thu,Fri,Sat','0,9,8,8,10,8,0');
INSERT INTO Multicolum_split VALUES(2,'2020-08-31','Sun,Mon,Tue,Wed,Thu,Fri','0,5,10,8,4,7');
INSERT INTO Multicolum_split VALUES(3,'2020-08-31','Sun,Mon,Tue,Wed,Thu,Fri,Sat','0,5,10,8,4,7');
SELECT
Id,
Name,
week_start_dt,
CAST(regexp_substr(days,'[^,]+',1,day_of_calendar) AS VARCHAR(10)) day_of_week,
CAST(regexp_substr(hours_of_work,'[^,]+',1,day_of_calendar) AS INTEGER) hours_of_work,
ROW_NUMBER() OVER(PARTITION BY id,week_start_dt ORDER BY day_of_calendar ) AS row_id
FROM sys_calendar.calendar,
Multicolum_split
WHERE day_of_calendar <= (CHAR(days) - CHAR(oreplace(days,',','')))+1;
Output:
Id week_start_dt day_of_week hours_of_work row_id
--- ------------- ----------- ------------- --------
1 20/08/31 Sun 0 1
1 20/08/31 Mon 9 2
1 20/08/31 Tue 8 3
1 20/08/31 Wed 8 4
1 20/08/31 Thu 10 5
1 20/08/31 Fri 8 6
1 20/08/31 Sat 0 7
2 20/08/31 Sun 0 1
2 20/08/31 Mon 5 2
2 20/08/31 Tue 10 3
2 20/08/31 Wed 8 4
2 20/08/31 Thu 4 5
2 20/08/31 Fri 7 6
3 20/08/31 Sun 0 1
3 20/08/31 Mon 5 2
3 20/08/31 Tue 10 3
3 20/08/31 Wed 8 4
3 20/08/31 Thu 4 5
3 20/08/31 Fri 7 6
3 20/08/31 Sat ? 7