Il magico Pivot nel mondo SQL

Creare un report comodo da leggere, a volte è un problema, ma una soluzione potrebbe essere la funzione PIVOT di

UN ESEMPIO

Supponiamo di aver realizzato un sistema per il controllo accessi. Ogni volta che il badge viene letto dal sensore RFID il suo codice, assieme a quello della porta e all’orario, viene scritto su una tabella.

Creiamo quindi la tabella di registrazione:

CREATE TABLE MREGACC (
 IDCART NUMBER, 
 MPORTA NUMBER, 
 MWHEN DATE);

I campi, pochi, sono l’identificativo del badge (IDCART), il numero della porta o varco e quando si è verificato l’evento. Ora inseriamo dei dati di prova:

INSERT INTO MREGACC (IDCART, MPORTA, MWHEN ) 
 values (1,1,to_date('12.04.2018','dd.mm.yyyy'));
INSERT INTO MREGACC (IDCART, MPORTA, MWHEN ) 
 VALUES (1,2,TO_DATE('12.04.2018','dd.mm.yyyy'));
INSERT INTO MREGACC (IDCART, MPORTA, MWHEN ) 
 VALUES (1,3,TO_DATE('12.04.2018','dd.mm.yyyy'));
INSERT INTO MREGACC (IDCART, MPORTA, MWHEN ) 
 VALUES (1,1,TO_DATE('12.04.2018','dd.mm.yyyy'));
INSERT INTO MREGACC (IDCART, MPORTA, MWHEN ) 
 values (1,1,to_date('12.04.2018','dd.mm.yyyy'));
INSERT INTO MREGACC (IDCART, MPORTA, MWHEN ) 
 VALUES (1,1,TO_DATE('12.04.2018','dd.mm.yyyy'));
INSERT INTO MREGACC (IDCART, MPORTA, MWHEN ) 
 values (1,1,to_date('12.04.2018','dd.mm.yyyy'));
INSERT INTO MREGACC (IDCART, MPORTA, MWHEN ) 
 VALUES (1,1,TO_DATE('12.04.2018','dd.mm.yyyy'));
INSERT INTO MREGACC (IDCART, MPORTA, MWHEN ) 
 values (1,1,to_date('12.04.2018','dd.mm.yyyy'));
INSERT INTO MREGACC (IDCART, MPORTA, MWHEN ) 
 VALUES (1,1,TO_DATE('12.04.2018','dd.mm.yyyy'));
INSERT INTO MREGACC (IDCART, MPORTA, MWHEN ) 
 values (2,1,to_date('12.04.2018','dd.mm.yyyy'));
INSERT INTO MREGACC (IDCART, MPORTA, MWHEN ) 
 VALUES (2,4,TO_DATE('12.04.2018','dd.mm.yyyy'));
INSERT INTO MREGACC (IDCART, MPORTA, MWHEN ) 
 VALUES (2,3,TO_DATE('12.04.2018','dd.mm.yyyy'));
INSERT INTO MREGACC (IDCART, MPORTA, MWHEN ) 
 VALUES (2,4,TO_DATE('12.04.2018','dd.mm.yyyy'));
INSERT INTO MREGACC (IDCART, MPORTA, MWHEN ) 
 VALUES (3,1,TO_DATE('12.04.2018','dd.mm.yyyy'));
INSERT INTO MREGACC (IDCART, MPORTA, MWHEN ) 
 VALUES (3,2,TO_DATE('12.04.2018','dd.mm.yyyy'));
INSERT INTO MREGACC (IDCART, MPORTA, MWHEN ) 
 values (3,2,to_date('12.04.2018','dd.mm.yyyy'));
INSERT INTO MREGACC (IDCART, MPORTA, MWHEN ) 
 VALUES (3,2,TO_DATE('12.04.2018','dd.mm.yyyy'));
INSERT INTO MREGACC (IDCART, MPORTA, MWHEN ) 
 values (3,3,to_date('12.04.2018','dd.mm.yyyy'));
INSERT INTO MREGACC (IDCART, MPORTA, MWHEN ) 
 VALUES (3,3,TO_DATE('12.04.2018','dd.mm.yyyy'));
INSERT INTO MREGACC (IDCART, MPORTA, MWHEN ) 
 VALUES (3,4,TO_DATE('12.04.2018','dd.mm.yyyy'));
COMMIT;

A questo punto, dopo il commit, avremo dei dati da poter estrarre:

SELECT IDCART, MPORTA, MWHEN
FROM MREGACC
WHERE 1=1
AND MWHEN=TO_DATE('12.04.2018','dd.mm.yyyy');

e il risultato sarà:

 IDCART MPORTA MWHEN 
---------- ---------- ---------
 1 1 12-APR-18 
 1 2 12-APR-18 
 1 3 12-APR-18 
 1 1 12-APR-18 
 1 1 12-APR-18 
 1 1 12-APR-18 
 1 1 12-APR-18 
 1 1 12-APR-18 
 1 1 12-APR-18 
 1 1 12-APR-18 
 2 1 12-APR-18 
 2 4 12-APR-18 
 2 3 12-APR-18 
 2 4 12-APR-18 
 3 1 12-APR-18....

MIGLIORARE

Una lista come la precedente è veritiera, ma certamente poco leggibile, e probabilmente poco utile. Supponiamo di voler sapere per ogni giorno,  quali accessi vengono eseguiti da ogni badge. In pratica dovremo raggruppare in base al numero di porta.

Ecco un esempio:

SELECT * FROM (
 SELECT IDCART BADGE,
 UNO_MYCOUNT+DUE_MYCOUNT+TRE_MYCOUNT+QUATTRO_MYCOUNT "NUM.REGISTR.",
 UNO_MYCOUNT "PORTA INGR.",DUE_MYCOUNT "PORTA MAG.",
 TRE_MYCOUNT "PORTA CED",QUATTRO_MYCOUNT "SALA RIUN."
 from (
 SELECT *
 from (
 SELECT IDCART, MPORTA
 from MREGACC WHERE MWHEN=TO_DATE('12.04.2018','dd.mm.yyyy')) T1
 PIVOT (COUNT(MPORTA) as MYCOUNT 
 FOR (MPORTA) IN (0 AS ZERO,1 AS UNO, 2 AS DUE, 3 AS TRE,4 AS QUATTRO)))
 );

e questo il risultato:

 BADGE NUM.REGISTR. PORTA INGR. PORTA MAG. PORTA CED SALA RIUN.
---------- ------------ ----------- ---------- ---------- ----------
 1     10            8          1           1         0 
 2      4            1          0           1         2 
 3      7            1          3           2         1

o in forma grafica:

CONCLUSIONE

La funzione PIVOT, in pratica ci permette di “ruotare” di 90° alcuni campi in modo da poter creare delle utili matrici e rendere più comprensibili i dati che forniamo.

 

 

 

Leave a Reply