Pivot and Unpivot
L’operatore UNPIVOT trasforma i dati disposti su una colonna in righe separate. Con questa utility è possibile convertire la visualizzazione dei dati per averli visualizzati su colonne e su righe. In questo esempio parliamo dei prezzi associati a un biglietto:
UNPIVOT :
SELECT * FROM tickets ; |
Applicazione dello unpivot: ruotando sulla colonna ticket è possibile vedere il prezzo di apertura e chiusura su righe separate.
SELECT ticket,to_char(ticket_date,'YYYYMMDD')ticket_date,price_type,price
FROM tickets
UNPIVOT ( price
FOR price_type
IN ( opening_price AS 'OPEN',
closing_price AS 'CLOSE') ) ;
Script:
drop table tickets purge
/
CREATE TABLE tickets (
ticket VARCHAR2 (20),
ticket_date DATE,
opening_price NUMBER,
closing_price NUMBER)
/
insert into tickets (ticket, ticket_date, opening_price, closing_price) VALUES (‘ABC214’,SYSDATE, 20, 229);
insert into tickets (ticket, ticket_date, opening_price, closing_price) VALUES (‘CDE215’,SYSDATE-12, 100, 200);
insert into tickets (ticket, ticket_date, opening_price, closing_price) VALUES (‘EFG215’,SYSDATE-200, 110, 121);
insert into tickets (ticket, ticket_date, opening_price, closing_price) VALUES (‘GHM216’,SYSDATE-200, 100, 103);
insert into tickets (ticket, ticket_date, opening_price, closing_price) VALUES (‘MNO216’,SYSDATE-201, 122, 11);
insert into tickets (ticket, ticket_date, opening_price, closing_price) VALUES (‘OPQ217’,SYSDATE-1, 49, 46);
insert into tickets (ticket, ticket_date, opening_price, closing_price) VALUES (‘QRS217’,SYSDATE-4, 55, 23);
insert into tickets (ticket, ticket_date, opening_price, closing_price) VALUES (‘STU218’,SYSDATE, 21, 78);
insert into tickets (ticket, ticket_date, opening_price, closing_price) VALUES (‘UVZ218’,SYSDATE-56, 23, 1);
insert into tickets (ticket, ticket_date, opening_price, closing_price) VALUES (‘ZZZ219’,SYSDATE-55, 80, 38);
COMMIT
/
PIVOT consente di trasformare la visualizzazione dei dati in base a singole righe aggregate in una singola riga. In questo esempio parliamo di uno stadio:
select ring,sector,qty from stadium ;
Il campo ad anello mostra i vari settori presenti al suo interno. Applicando il PIVOT è possibile avere i dati del campo ad anello aggregando la quantità dei singoli settori, la trasformazione permette di ottenere i dati in un’unica riga.
SELECT ring, SUM(DECODE(sector, ‘red’, qty, 0)) AS red_sum_quantity,
SUM(DECODE(sector, ‘green’, qty, 0)) AS green_sum_quantity,
SUM(DECODE(sector, ‘yellow’, qty, 0)) AS yellow_sum_quantity,
SUM(DECODE(sector, ‘blue’, qty, 0)) AS blue_sum_quantity
FROM stadium
GROUP BY ring ORDER BY ring ;
FROM (SELECT ring, sector, qty
FROM stadium)
PIVOT (SUM(qty) AS sum_sector FOR (sector) IN (‘red’ AS red, ‘green’ AS green, ‘yellow’ AS yellow, ‘blue’ AS blue)) ;
Per mostrare il risultato nel metodo di trasposizione:
SELECT *
FROM (SELECT sector, qty
FROM stadium)
PIVOT (SUM(qty) AS sum_sector FOR (sector) IN (‘red’ AS red, ‘green’ AS green, ‘yellow’ AS yellow, ‘blue’ AS blue)) ;
The same result we can get with out pivot option too:
SELECT SUM(DECODE(sector, ‘red’, qty, 0)) AS red_sum_quantity,
SUM(DECODE(sector, ‘green’, qty, 0)) AS green_sum_quantity,
SUM(DECODE(sector, ‘yellow’, qty, 0)) AS yellow_sum_quantity,
SUM(DECODE(sector, ‘blue’, qty, 0)) AS blue_sum_quantity
FROM stadium ORDER BY ring ;
