By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE all_data ( kind, time_period, amount ) AS
SELECT 'peter', DATE '2020-01-01', 23 FROM DUAL UNION ALL
SELECT 'maria', DATE '2020-02-01', 42 FROM DUAL UNION ALL
SELECT 'eric', DATE '2020-03-01', 11 FROM DUAL;
3 rows affected
DECLARE
TYPE array_t IS VARRAY(3) OF VARCHAR2(10);
ARRAY array_t := array_t('peter', 'maria', 'eric');
BEGIN
FOR i IN 1..ARRAY.COUNT LOOP
EXECUTE IMMEDIATE
'CREATE TABLE ' || ARRAY(i) || ' ( kind, time_period, amount_' || ARRAY(i) || ' ) AS'
|| ' SELECT kind, time_period, amount FROM all_data WHERE kind = ''' || ARRAY(i) || '''';
END LOOP;
END;
/
1 rows affected
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SELECT * FROM peter;
| KIND | TIME_PERIOD | AMOUNT_PETER |
|---|---|---|
| peter | 2020-01-01 00:00:00 | 23 |
SELECT * FROM maria;
| KIND | TIME_PERIOD | AMOUNT_MARIA |
|---|---|---|
| maria | 2020-02-01 00:00:00 | 42 |
SELECT * FROM eric;
| KIND | TIME_PERIOD | AMOUNT_ERIC |
|---|---|---|
| eric | 2020-03-01 00:00:00 | 11 |