Make your Data Dance UNPIVOT PIVOT and GROUP

Make your Data Dance UNPIVOT, PIVOT and GROUP BY Extensions Stew Ashton UKOUG Techfest 2019 Can you read the following line? If not, please move closer. It's much better when you can read the code ; )

Who am I? • 1981 -2015: – Developer / application architect – From Mainframe to client / server to Web • 2005 -present: focus on Oracle DB development – Advocate of data-centric application architecture – Contribute to asktom & ODC/OTN SQL forum – Presented at OOW, conferences, meetups… 2

Data with Subtotals? REGION COUNTRY CUSTOMER SALE_DATE QTY AMOUNT Europe DE Raina Silverberg 2017 -01 -23 2 110 Europe DE Raina Silverberg 2017 -02 -19 3 220 Europe DE Gloria Silverberg Raina Saintclair (Subtotal) 2017 -01 -27 2 5 140 330 Europe DE Gloria Saintclair 2017 -02 -26 2017 -01 -27 2 3 140 280 Europe DE Gloria Saintclair 2017 -02 -26 3 280 Europe DE Gloria Saintclair (Subtotal) 5 420 3

Totals and Grand Total? REGION COUNTRY CUSTOMER Europe DE Raina Silverberg 2017 -01 -23 2 110 Europe DE Raina Silverberg 2017 -02 -19 3 220 Europe DE Raina Silverberg (Subtotal) 5 330 Europe DE Gloria Saintclair 2017 -01 -27 2 140 Europe DE Gloria Saintclair 2017 -02 -26 3 280 Europe DE Gloria Saintclair (Subtotal) 5 420 Europe DE (Subtotal) 10 750 Europe (Total) 10 750 (Grand total) SALE_DATE QTY AMOUNT 4

Columns to Rows? A B C 1 1 A 1 B 1 C 2 2 A 2 B 2 C 3 3 A 3 B 3 C 4 1 B 5 2 B 6 3 B 7 1 C 8 2 C 9 3 C 5

Rows to Columns? A B C 1 1 A 4 A 7 A 2 2 A 5 A 8 A 3 3 A 6 A 9 A 4 4 A 5 5 A 6 6 A 7 7 A 8 8 A 9 9 A 6

Transpose? Rows to columns and Columns to rows? A 1 B 2 C 3 A 1 1 A 1 B 1 C B 2 2 A 2 B 2 C C 3 3 A 3 B 3 C 7

Rotate? A B C 1 1 A 1 B 1 C 2 2 A 2 B 2 C 3 3 A 3 B 3 C 8

How? • UNPIVOT columns to rows • PIVOT rows to columns • GROUP BY extensions – ROLLUP – CUBE – GROUPING SETS • GROUPING_ID() and GROUPING() functions 9

UNPIVOT: columns to rows KEY A B KEY COL VAL 1 1 A 1 B 1 A 1 A 1 B 1 B with data(KEY, A, B) as ( select 1, '1 A', '1 B' from dual ) select * from data unpivot(VAL for COL in(A, B)); Metadata becomes data 10

PIVOT: rows to columns KEY 'A' 'B' KEY COL VAL 1 1 A 1 B 1 A 1 A 1 COL values B produce 1 B Only listed output select * from data unpivot(VAL for COL in(A, B)); pivot(max(VAL) for COL in('A', 'B')); 1 C 1 C Data becomes metadata 11

Multiple Columns KEY A_N A_V B_N B_V KEY COL N V 1 1 VA 2 VB 1 A 1 VA 1 B 2 VB Round trip! select * from data unpivot((N, V) for COL in ((A_N, A_V) as 'A', (B_N, B_V) as 'B')) pivot( max(N) as N, max(V) as V for COL in ('A' as A, 'B' as B)); 12

PIVOT generates column names KEY AN A_N AV A_V BN B_N BV B_V 1 1 VA 2 VB pivot( max(N) as N, max(V) as V for COL in ('A' as A, 'B' as B) ); 13

Transpose! select * from t unpivot(x for nk in(a, b, c)) pivot(max(x) for k in(1, 2, 3)); NK K A 1 B 2 C 3 A 1 1 A 1 B 1 C B 2 2 A 2 B 2 C C 3 3 A 3 B 3 C 14

Matrix: rotate! with normalized as ( Normalize to cells (row, column, value), select k r, c, v from t rotate, display unpivot(v for c in( a as 1, b as 2, c as 3 A B )) ) 1 1 A 1 B , rotated as ( select 4 -c r, r c, v from normalized 2 2 A 2 B ) select * from rotated 3 3 A 3 B pivot(max(v) for c in(1, 2, 3)) order by r; C 1 C 2 C 3 C 15

Grouping Sales History • • • 2 regions 2 countries per region 2 customers per country 2 years 2 sales per year per customer 16

select sum(amount) amount from sales_yr; AMOUNT 18360 17

select sum(amount) amount from sales_yr group by (); AMOUNT 18360 18

select region, sum(amount) amount from sales_yr group by region; What if I want both? REGION AMOUNT Americas 9720 Europe 8640 19

select region, sum(amount) amount from sales_yr group by grouping sets ( (), region ); REGION AMOUNT Americas 9720 Europe 8640 18360 20

select region, country, sum(amount) amount from sales_yr group by grouping sets ( (), region, (region, country) ); What if I want 3 levels? REGION COUNTRY AMOUNT Europe DE 1500 Europe FR 7140 Europe 8640 Americas CA 8820 Americas US 900 Americas 9720 18360 21

select region, country, sum(amount) amount from sales_yr group by rollup(region, country); REGION COUNTRY AMOUNT Europe DE 1500 Europe FR 7140 Europe 8640 Americas CA 8820 Americas US 900 Americas 9720 18360 22

select region, country, sum(amount) amount from sales_yr group by cube(region, country); What if I have NULL data? REGION COUNTRY AMOUNT 18360 CA 8820 DE 1500 FR 7140 US 900 Europe 8640 Europe DE 1500 Europe FR 7140 Americas 9720 Americas CA 8820 Americas US 900 23

select region, country, sum(amount) amount from sales_yr group by cube(region, country); REGION COUNTRY AMOUNT 18360 CA 8820 DE 1500 FR 7140 US 900 Europe 8640 Europe DE 1500 Europe FR 7140 Americas 9720 Americas CA 8820 Americas US 900 24

select grouping(country) g_c, region, country, sum(amount) amount from sales_yr group by cube(region, country); G_C 1 0 0 REGION COUNTRY AMOUNT 18360 CA 8820 DE 1500 FR 7140 US 900 Europe 8640 Europe DE 1500 Europe FR 7140 Americas 9720 Americas CA 8820 Americas US 900 25

select grouping(region)*2 g_r, grouping(country) g_c, region, country, sum(amount) amount from sales_yr group by cube(region, country); G_R G_C REGION COUNTRY AMOUNT 2 1 18360 2 0 CA 8820 2 0 DE 1500 2 0 FR 7140 2 0 US 900 0 1 Europe 8640 0 0 Europe DE 1500 0 0 Europe FR 7140 0 1 Americas 9720 0 0 Americas CA 8820 0 0 Americas US 900 26

select grouping_id(region, country) g_rc, grouping(region)*2 g_r, G_RC G_R G_C REGION COUNTRY AMOUNT grouping(country) g_c, 3 2 1 18360 region, country, 2 2 0 CA 8820 sum(amount) amount 2 2 0 DE 1500 2 2 0 FR 7140 from sales_yr 2 2 0 US 900 group by cube(region, country); 1 0 1 Europe 8640 0 Europe DE 1500 0 Europe FR 7140 1 Americas 9720 0 Americas CA 8820 0 Americas US 900 27

Spread. Sheet-like Data from EMP select deptno, * from ( select job, sum(sal) deptno, sal job, sal from emp ) from emp pivot(sum(sal) group by deptno, for job; deptno in( 10 as "10", 20 as "20", 30 as "30" )); JOB 10 ANALYST CLERK 1300 MANAGER 2450 PRESIDENT 5000 SALESMAN 20 30 6000 1900 950 2975 2850 5600 DEPTNO JOB 10 CLERK 10 MANAGER 10 PRESIDENT 20 CLERK 20 ANALYST 20 MANAGER 30 CLERK 30 MANAGER 30 SALESMAN SAL 1300 2450 5000 1900 6000 2975 950 2850 5600 28

DEPTNO JOB SAL 29025 4150 6000 8275 5600 5000 8750 1300 2450 5000 10875 1900 6000 2975 9400 950 2850 5600 Spread. Sheet-like Data from. CLERK EMP select deptno, job, sum(sal) sal from emp deptno, group job, by sum(sal) cube(deptno, sal job); JOB 10 20 30 TOTAL 6000 1900 950 4150 2975 2850 8275 5000 5600 10875 9400 29025 from emp ANALYST 6000 group by cube(deptno, job); CLERK MANAGER PRESIDENT SALESMAN (Total) 1300 2450 5000 8750 ANALYST MANAGER SALESMAN PRESIDENT 10 10 CLERK 10 MANAGER 10 PRESIDENT 20 20 CLERK 20 ANALYST 20 MANAGER 30 30 CLERK 30 MANAGER 30 SALESMAN 29

select case grouping(deptno) when 1 then -1 else deptno end deptno, job, sum(sal) sal from emp group by cube(deptno, job); DEPTNO JOB -1 -1 CLERK -1 ANALYST -1 MANAGER -1 SALESMAN -1 PRESIDENT 10 10 CLERK 10 MANAGER 10 PRESIDENT 20 20 CLERK 20 ANALYST 20 MANAGER 30 30 CLERK 30 MANAGER 30 SALESMAN SAL 29025 4150 6000 8275 5600 5000 8750 1300 2450 5000 10875 1900 6000 2975 9400 950 2850 5600 30

select case grouping(deptno) when 1 then -1 else deptno end deptno, job, sum(sal) sal, grouping(job) gr_job from emp group by cube(deptno, job); DEPTNO JOB -1 -1 CLERK -1 ANALYST -1 MANAGER -1 SALESMAN -1 PRESIDENT 10 10 CLERK 10 MANAGER 10 PRESIDENT 20 20 CLERK 20 ANALYST 20 MANAGER 30 30 CLERK 30 MANAGER 30 SALESMAN SAL GR_JOB 29025 1 4150 0 6000 0 8275 0 5600 0 5000 0 8750 1 1300 0 2450 0 5000 0 10875 1 1900 0 6000 0 2975 0 9400 1 950 0 2850 0 5600 31 0

select case gr_job when 1 then '(Total)' else job end job, "10", "20", "30", "(Total)" from ( select case grouping(deptno) when 1 then -1 else deptno end deptno, job, sum(sal) sal, grouping(job) gr_job from emp group by cube(deptno, job) ) pivot(max(sal) for deptno in ( 10, 20, 30, -1 as TOTAL )) order by gr_job, job; JOB ANALYST CLERK MANAGER PRESIDENT SALESMAN (Total) 10 1300 2450 5000 8750 20 30 TOTAL 6000 1900 950 4150 2975 2850 8275 5000 5600 10875 9400 29025 32

Spread. Sheet-like Data from SALES • 2 sums – QTY, AMOUNT • 4 vertical levels – Grand total, region, country, customer • 2 horizontal levels – Year, total all years 33

select sum(QTY) QTY, sum(AMOUNT) AMOUNT from sales_yr group by grouping sets( rollup(REGION, COUNTRY, CUSTOMER, YR), rollup(YR, REGION, COUNTRY) ) 34

select decode(grouping_id(REGION, COUNTRY, CUSTOMER), 7, 'Total', REGION) REGION, decode(grouping_id(REGION, COUNTRY, CUSTOMER), 3, 'Total', COUNTRY) COUNTRY, decode(grouping_id(REGION, COUNTRY, CUSTOMER), 1, 'Total', CUSTOMER) CUSTOMER, decode(grouping(YR), 1, 'Total', YR) YR, sum(QTY) QTY, sum(AMOUNT) AMOUNT from sales_yr group by grouping sets( rollup(REGION, COUNTRY, CUSTOMER, YR), rollup(YR, REGION, COUNTRY) ) 35

select * from ( select decode(grouping_id(REGION, COUNTRY, CUSTOMER), 7, 'Total', REGION) REGION, decode(grouping_id(REGION, COUNTRY, CUSTOMER), 3, 'Total', COUNTRY) COUNTRY, decode(grouping_id(REGION, COUNTRY, CUSTOMER), 1, 'Total', CUSTOMER) CUSTOMER, decode(grouping(YR), 1, 'Total', YR) YR, sum(QTY) QTY, sum(AMOUNT) AMOUNT from sales_yr group by grouping sets( rollup(REGION, COUNTRY, CUSTOMER, YR), rollup(YR, REGION, COUNTRY) ) ) pivot(max(qty) as q, max(amount) as a for (yr) in ( ('2016') as "2016", ('2017') as "2017", ('Total') as "Total" )) order by nullif(region, 'Total'), nullif(country, 'Total'), nullif(customer, 'Total') 36

REGION COUNTRY CUSTOMER 2016_Q 2016_A 2017_Q 2017_A Total_Q Total_A Americas CA Alyce Gray 13 1530 26 3060 Americas CA Salena Grace 21 2880 42 5760 Americas CA (Total) 34 4410 68 8820 Americas US Linette Ingram 3 210 6 420 Americas US Vida Puleo 3 240 6 480 Americas US (Total) 6 450 12 900 Americas (Total) 40 4860 80 9720 Europe DE Gloria Saintclair 5 420 10 840 Europe DE Raina Silverberg 5 330 10 660 Europe DE (Total) 10 750 20 1500 Europe FR Koren Macdonald 17 2100 34 4200 Europe (Total) FR Madelaine Gottlieb FR (Total) 11 28 38 78 1470 3570 4320 9180 22 2940 56 7140 76 8640 156 18360 37

Make your Data Dance UNPIVOT, PIVOT and GROUP BY Extensions Stew Ashton UKOUG Techfest 2019 Blog: stewashton. wordpress. com Twitter: @stewashton https: //www. slideshare. net/search/slideshow? &q=stewashton

UNPIVOT: Intersecting Ranges # ranges 1 2 3 2 1 1 (null) 2 C 5 3 1 4 3 2 O 4 5 # ranges 1 2 1 (null) 1 39

select * from range_input OBJ_ID F T C 1(null) C 25 C 34 O 13 O 24 O 5(null) 40

select * from range_input unpivot ( F for FT in (F as 1, T as -1) ) OBJ_ID FT F C 1(null) 11 C 25 12 C 34 -15 O 13 C O 24 C -14 O 5(null) 11 O -13 O 12 O -14 O 15 41

select * from range_input unpivot include nulls ( F for FT in (F as 1, T as -1) ) OBJ_ID FT F C 11 C -1(null) 12 C -15 13 C -14 13 O C -14 11 O -13 12 O -14 15 O -1(null) 42

with unpivoted as ( select * from range_input unpivot include nulls ( F for FT in (F as 1, T as -1) ) ) select OBJ_ID, F, T, NUM_R from ( select a. *, lead(F) over(partition by obj_id order by F) T, sum(FT) over(partition by obj_id order by F) NUM_R from unpivoted a ) where F < nvl(T, F+1) and NUM_R > 0 order by obj_id, T; OBJ_ID C C C O O F 1 2 3 4 5 1 2 3 5 T 2 3 4 5 (null) 2 3 4 (null) NUM_R 1 2 3 2 1 1 43