analytics-PIVOT-and-UNPIVOT-functions.sql

REM Script: Analytics - PIVOT and UNPIVOT functions
REM SQL from the KISS (Keep It Simply SQL) Analytic video series by Developer Advocate Connor McDonald. This script demonstrates the PIVOT and UNPIVOT functions.

Run this script standalone, or take it as part of the complete Analytics class at https://tinyurl.com/devgym-classes

select to_char(trunc(s.time_id,'Q'),'MON') quarter,
 prod_category,
count(*)
fromsh.sales s,
sh.products p
wheres.prod_id=p.prod_id
ands.time_Id>=date'2000-01-01'
ands.time_Id<date'2001-01-01'
group byp.prod_category,
 to_char(trunc(s.time_id,'Q'),'MON')
order by2,to_date(to_char(trunc(s.time_id,'Q'),'MON'),'MON');

select prod_category,jan,apr,jul,oct
from (
select to_char(trunc(s.time_id,'Q'),'MON') quarter,
 prod_category
fromsh.sales s,
sh.products p
wheres.prod_id=p.prod_id
ands.time_Id>=date'2000-01-01'
ands.time_Id<date'2001-01-01'
)
pivot ( count(*) for quarter in
( 'JAN'as jan,'APR'as apr,'JUL'as jul,'OCT'as oct ) )
order by1;

droptable pivoted_sales purge;

createtablepivoted_salesas
select prod_category,jan,apr,jul,oct
from (
select to_char(trunc(s.time_id,'Q'),'MON') quarter,
 prod_category
fromsh.sales s,
sh.products p
wheres.prod_id=p.prod_id
ands.time_Id>=date'2000-01-01'
ands.time_Id<date'2001-01-01'
)
pivot ( count(*) for quarter in
( 'JAN'as jan,'APR'as apr,'JUL'as jul,'OCT'as oct ) )
order by1;

select prod_category, quarter, quantity
from pivoted_sales
unpivot
( quantity for quarter in (JAN,APR,JUL,OCT) )
order by1,to_date(quarter,'MON');

update pivoted_sales
set oct =null
where prod_category ='Hardware';

commit


select prod_category, quarter, quantity
from pivoted_sales
unpivot
( quantity for quarter in (JAN,APR,JUL,OCT) )
order by1,to_date(quarter,'MON');

select prod_category, quarter, quantity
from pivoted_sales
unpivot include nulls
( quantity for quarter in (JAN,APR,JUL,OCT) )
order by1,to_date(quarter,'MON');