Pivot in Oracle

Pivot functie

select * from
(select bsn,wet, voorzieningsoort  from controlelijst_beschikte_voorziening_jgd_2017)
pivot
(count(distinct wet)  for voorzieningsoort in
('Jeugd - Ambulante jeugdzorg','Jeugd - Begeleiding individueel en groep','Jeugd - Behandeling (L)VB','Jeugd - Dagbehandeling jeugdzorg','Jeugd -
Generalistische basis GGZ','Jeugd - Gezinsmanagement','Jeugd - Kortdurend verblijf','Jeugd - Ondersteuning bij opstellen familiegroepsplan',
'Jeugd - Persoonlijke verzorging','Jeugd - Pleegzorg','Jeugd - Specialistische GGZ','Jeugd - Verblijf jeugdzorg','Jeugd - Verblijf (L)VB' )
);

bij pivot altijd een lijst van waardes van het pivotveld die als veldnaam gebruikt worden



Makkelijker is een speciale dynamische functie te maken die dynamisch een lijst van alle mogelijke waardes opbouwt.





create or replace procedure createPivotTable (source_table VARCHAR2, NON_PIVOT_FIELDNAME VARCHAR2, PIVOT_FIELDNAME varchar2,target_table VARCHAR2) AUTHID CURRENT_USER IS
        -- NON_PIVOT_FIELDNAME bijv bsn
        -- PIVOT_FIELDNAME bijv voorzieningsoort
        -- werkt alleen als er een veld wet is in de tabel source_tabel, anders toevoegen
        cols VARCHAR2(1000);
        sql_get_colums varchar2(1000) :=   'SELECT SUBSTR (SYS_CONNECT_BY_PATH (' || PIVOT_FIELDNAME ||  ', '''''',''''''), 3) || ''''''''
                                                FROM (SELECT piv.*
                                                ,      ROW_NUMBER () OVER (ORDER BY ' || PIVOT_FIELDNAME ||  ' ) AS rn
                                                ,      COUNT (*) OVER () AS cnt
                                                FROM (
                                                        SELECT DISTINCT ' || PIVOT_FIELDNAME ||  '
                                                        FROM ' || source_table || '
                                                    ) piv)
                                                WHERE rn = cnt
                                                START WITH ROWNUM  = 1
                                                CONNECT BY rn = PRIOR rn + 1';
        s_sql               VARCHAR2(1000)              ; --:= REPLACE(sql_get_colums, '@TBL_NAME', source_table);
        t_sql               VARCHAR2(1000);
   begin
        s_sql:=sql_get_colums;
        EXECUTE IMMEDIATE s_sql INTO cols;
        --cols bevat een lijst met alle distinct waardes van de Pivot_fieldname tussen quotes en gescheiden door een komma
        -- bijv  'Jeugd - Ambulante jeugdzorg','Jeugd - Begeleiding individueel en groep','Jeugd - Behandeling (L)VB'
        -- drop table indien bestaat
        BEGIN
            EXECUTE IMMEDIATE 'DROP TABLE ' || target_table  ;
        EXCEPTION
           WHEN OTHERS THEN
              IF SQLCODE != -942 THEN
                 RAISE;
              END IF;
        END;
       commit;
  --dbms_output.put_line (vzlist );
        t_sql := 'create table ' || target_table || ' as select * from (select  ' || NON_PIVOT_FIELDNAME ||  ' ,wet, ' || PIVOT_FIELDNAME ||  '  from ' || source_table || ')  pivot (count(distinct wet)  for ' || PIVOT_FIELDNAME ||  ' in (' || cols || ' ))';
    dbms_output.put_line (t_sql );
    execute immediate t_sql;
    commit;
--    EXCEPTION
--         WHEN OTHERS THEN return -99;
    END createPivotTable;