back

next

 

 

 

 

Pivot, Four Ways

 

 

It's been over 20 years since Microsoft Excel 5 introduced functionality called PivotTable. 2015 sees APEX 5 introduce pivot functionality into the product.

 

Pivot operators take data that's available as separate rows and converts it to columns. The classic example might take sales data aggregated to monthly totals, then pivot those totals so each month has it's own column.

 

This article will demonstrate four ways to snack on pivot in the Oracle world, and I add a little extra spice to each example – perhaps something you haven't seen in action before.

 

 

SQL prior to 11g

 

Pivoting data in earlier versions of Oracle wasn't too difficult, it just required some repetition of code. Consider the requirement to show a count of job types by department, where the jobs are the data to be pivoted as columns in the output.

 

A standard group by shows the data as it stands in the sample schema.

select deptno, job, count(*)

from emp

group by job, deptno

order by deptno;

 

DEPTNO JOB COUNT(*)

10 CLERK 1

10 MANAGER 1

10 PRESIDENT 1

20 ANALYST 2

20 CLERK 2

20 MANAGER 1

30 CLERK 1

30 MANAGER 1

30 SALESMAN 4

 

To pivot this data, a set of columns conditionally sums the data, all varied slightly by expression operands.

select deptno

 ,(select dname from dept d where d.deptno = e.deptno) dname

 ,sum(case when job = 'ANALYST' then 1 end) ANALYST

 ,sum(case when job = 'CLERK' then 1 end) CLERK

 ,sum(case when job = 'MANAGER' then 1 end) MANAGER

 ,sum(case when job = 'PRESIDENT' then 1 end) PRESIDENT

 ,sum(case when job = 'SALESMAN' then 1 end) SALESMAN

 ,count(*) total

from emp e

group by rollup(deptno)

 

order by deptno;

 

Obtaining a total was as simple as including an unconditional count of all rows for that grouped department. Grand totals are provided by adding ROLLUP() to the group by. Also included was the department name, provided by a scalar subquery instead of a normal join.

 

 

Analysing Pivot

 

11g introduced a more elegant solution for this problem, new syntax to handle such a requirement, though the number of columns is still fixed within the query definition.

select deptno -- row column

  ,analyst, clerk, manager, president, salesman -- pivoted columns

  ,total, dname -- data carried through

from (

  select deptno, job, count(*) c

    ,(select dname from dept d where d.deptno = e.deptno) dname

    ,sum(count(*)) over (partition by deptno) as total

  from emp e

  group by job, deptno

)

pivot

 (sum(c)

  for job in (

   'ANALYST' as analyst

  ,'CLERK' as clerk

  ,'MANAGER' as manager

  ,'PRESIDENT' as president

  ,'SALESMAN' as salesman)

)

order by deptno

 

The original query becomes the from clause query. The job in the expression is now moved to the IN list within the pivot, and it's summing the count for each group. Column aliases are provided for each job type.

 

To include a department total column needs a little bit of analytical magic. The analytical function actually goes in the inner query, to predetermine the total number of employees for each department. The total is carried through to the pivoted data set as another literal column, the same is done with the department name.

 

Pivoting needs are everywhere and with some basic analytics it's possible to provide everything the user may need.

 

 

 advertisement

 

 

Unpivot

 

Unpivot was also introduced at the same time, but may have stayed hidden in the shadow of PIVOT partially because it's only useful for data that's not completely normalised.

 

For example, demo_customers has two phone number columns. UNPIVOT allows these to be represented as separate rows. Phone_number is the new column which could be likened to a union of the two original phone number columns. The rec_type column is also introduced, indicating the source for each row.

 

select customer_id, cust_first_name

  ,phone_number, rec_type

from demo_customers

unpivot (

 phone_number

  for rec_type in (phone_number1,phone_number2)

);

 

CUSTOMER_ID CUST_FIRST_NAME PHONE_NUMBER REC_TYPE

1 John 703-555-2143 PHONE_NUMBER1

1 John 703-555-8967 PHONE_NUMBER2

2 William 404-555-3285 PHONE_NUMBER1

3 Edward 617-555-3295 PHONE_NUMBER1

4 Frank 773-555-7693 PHONE_NUMBER1

5 Fiorello 212-555-3923 PHONE_NUMBER1

6 Albert 314-555-4022 PHONE_NUMBER1

7 Eugene 860-555-1835 PHONE_NUMBER1

 

Don't need to worry about a from clause subquery, just unpivot directly and select the new columns directly.

 

Unpivots come in handy in unexpected places. Turning column data into rows can help make search facilities easier. Tables with plenty of key dates per record can be turned into row data to feed charting tools.

 

 

APEX 5 Pivot

 

Interactive reports in APEX 5 have undertaken nothing short of a complete rewrite. On top of this the APEX team have provided a pivot action for the user, upon the click of a few buttons on a simple report.

 

 

This action opens a dialog to select the columns to pivot, which columns for the row and what is to be calculated. A tidy report total can be shown at the click of a checkbox.

 

 

The final result is neatly presented and additional calculations and row columns can be added easily by the end user.

 

 

Conclusion

 

Pivoting queries provided by Oracle show just how versatile native SQL can be. Oracle could pivot since before it knew how, then Oracle 11g introduced a method to do it both directions.

 

APEX 5 takes this all a step further by making this accessible to end users in their own reports, all at the click of a few buttons.

 

Talking about any functionality in Oracle always brings up opportunity to demonstrate related features. Analytical functions, data warehousing rollups and now one of the most advanced web IDEs ever delivered offers the user pivot capability at their own fingertips.

 

Keep an eye out for the extra things Oracle might provide to help make your life easier.

 

OTECH MAGAZINE #7  spring 2015        copyright otech magazine 2015

www.otechmag.com