Analytics - PIVOT and UNPIVOT functions

View All Scripts Login to Run Script

Script Name Analytics - PIVOT and UNPIVOT functions
Description 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
Area SQL General / Analytics
Contributor Connor Mcdonald (Oracle)
Created Wednesday May 25, 2016

Statement 1

select to_char(trunc(s.time_id,'Q'),'MON') quarter,
       prod_category,
       count(*)
from sh.sales s,
     sh.products p
where s.prod_id = p.prod_id
and   s.time_Id >= date '2000-01-01'
and   s.time_Id < date '2001-01-01'
group by p.prod_category,
         to_char(trunc(s.time_id,'Q'),'MON')
order by 2,to_date(to_char(trunc(s.time_id,'Q'),'MON'),'MON')

QUARTER	PROD_CATEGORY	COUNT(*)
JAN	Electronics	6398
APR	Electronics	8709
JUL	Electronics	10283
OCT	Electronics	10771
JAN	Hardware	944
APR	Hardware	696
JUL	Hardware	740
OCT	Hardware	708
JAN	Peripherals and Accessories	17923
APR	Peripherals and Accessories	12627
JUL	Peripherals and Accessories	14254
OCT	Peripherals and Accessories	14047
JAN	Photo	6501
APR	Photo	7213
JUL	Photo	7526
OCT	Photo	6805
JAN	Software/Other	30431
APR	Software/Other	26270
JUL	Software/Other	26147
OCT	Software/Other	23653

20 rows selected.

Statement 2

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

PROD_CATEGORY	JAN	APR	JUL	OCT
Electronics	6398	8709	10283	10771
Hardware	944	696	740	708
Peripherals and Accessories	17923	12627	14254	14047
Photo	6501	7213	7526	6805
Software/Other	30431	26270	26147	23653

5 rows selected.

Statement 3
```
drop table pivoted_sales purge
```
ORA-00942: table or view does not exist

Statement 4

create table pivoted_sales as
select prod_category,jan,apr,jul,oct
from (
  select to_char(trunc(s.time_id,'Q'),'MON') quarter,
         prod_category
  from sh.sales s,
       sh.products p
  where s.prod_id = p.prod_id
  and   s.time_Id >= date '2000-01-01'
  and   s.time_Id < date '2001-01-01'
)
pivot ( count(*) for quarter in 
( 'JAN' as jan,'APR' as apr,'JUL' as jul,'OCT' as oct ) )
order by 1

Table created.

Statement 5

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

PROD_CATEGORY	QUARTER	QUANTITY
Electronics	JAN	6398
Electronics	APR	8709
Electronics	JUL	10283
Electronics	OCT	10771
Hardware	JAN	944
Hardware	APR	696
Hardware	JUL	740
Hardware	OCT	708
Peripherals and Accessories	JAN	17923
Peripherals and Accessories	APR	12627
Peripherals and Accessories	JUL	14254
Peripherals and Accessories	OCT	14047
Photo	JAN	6501
Photo	APR	7213
Photo	JUL	7526
Photo	OCT	6805
Software/Other	JAN	30431
Software/Other	APR	26270
Software/Other	JUL	26147
Software/Other	OCT	23653

20 rows selected.

Statement 6

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

1 row(s) updated.

Statement 7
```
commit
```
Statement processed.

Statement 8

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

PROD_CATEGORY	QUARTER	QUANTITY
Electronics	JAN	6398
Electronics	APR	8709
Electronics	JUL	10283
Electronics	OCT	10771
Hardware	JAN	944
Hardware	APR	696
Hardware	JUL	740
Peripherals and Accessories	JAN	17923
Peripherals and Accessories	APR	12627
Peripherals and Accessories	JUL	14254
Peripherals and Accessories	OCT	14047
Photo	JAN	6501
Photo	APR	7213
Photo	JUL	7526
Photo	OCT	6805
Software/Other	JAN	30431
Software/Other	APR	26270
Software/Other	JUL	26147
Software/Other	OCT	23653

19 rows selected.

Statement 9

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

PROD_CATEGORY	QUARTER	QUANTITY
Electronics	JAN	6398
Electronics	APR	8709
Electronics	JUL	10283
Electronics	OCT	10771
Hardware	JAN	944
Hardware	APR	696
Hardware	JUL	740
Hardware	OCT	-
Peripherals and Accessories	JAN	17923
Peripherals and Accessories	APR	12627
Peripherals and Accessories	JUL	14254
Peripherals and Accessories	OCT	14047
Photo	JAN	6501
Photo	APR	7213
Photo	JUL	7526
Photo	OCT	6805
Software/Other	JAN	30431
Software/Other	APR	26270
Software/Other	JUL	26147
Software/Other	OCT	23653

20 rows selected.

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database