12. Reporting and Reshaping

Pivoting a Result Set into One Row

We want to take values from groups of rows and turn those values into columns in a single row per group.

Sample Input

Sample Output

select 
    sum(case when deptno=10 then 1 else 0 end) as deptno_10,
    sum(case when deptno=20 then 1 else 0 end) as deptno_20,
    sum(case when deptno=30 then 1 else 0 end) as deptno_30
from emp

Pivoting a Result Set into Multiple Rows

We want to turn rows into columns by creating a column corresponding to each of the values in a single given column.

Sample Input

Sample Output

select 
    max(case when job='CLERK' then ename else null end) as clerks,
    max(case when job='ANALYST' then ename else null end) as analysts,
    max(case when job='MANAGER' then ename else null end) as mgrs,
    max(case when job='PRESIDENT' then ename else null end) as prez,
    max(case when job='SALESMAN' then ename else null end) as sales
from (
 select job,
 ename,
 row_number()over(partition by job order by ename) rn
 from emp
) x
group by rn

Explanation

Step 1:

select job,
ename,
row_number()over(partition by job order by ename) rn
from emp

Step 2:

select rn,
case when job='CLERK' then ename else null end as clerks,
case when job='ANALYST' then ename else null end as analysts,
case when job='MANAGER' then ename else null end as mgrs,
case when job='PRESIDENT' then ename else null end as prez,
case when job='SALESMAN' then ename else null end as sales
from (
select job,
ename,
row_number()over(partition by job order by ename) rn
from emp
) x

Note the blank spaces are null value. To remove the NULLs, use the aggregate function MAX or MIN and group by RN.

Reverse Pivoting a Result Set

We want to transform columns to rows.

Sample Input

Sample Output

select 
    dept.deptno,
    case dept.deptno
        when 10 then emp_cnts.deptno_10
        when 20 then emp_cnts.deptno_20
        when 30 then emp_cnts.deptno_30
        end as counts_by_dept
from emp_cnts cross join
(select deptno from dept where deptno <= 30) dept

Explanation

select * from EMP_CNTS;

There are three columns, and we need to create three rows. Begin by creating a Cartesian product between inline view EMP_CNTS and some table expression that has at least three rows. Following sample code uses table DEPT to create the Cartesian product. The Cartesian product enables you to return a row for each column in inline view EMP_CNTS.

select dept.deptno,
    emp_cnts.deptno_10,
    emp_cnts.deptno_20,
    emp_cnts.deptno_30
from (
    Select 
        sum(case when deptno=10 then 1 else 0 end) as deptno_10,
        sum(case when deptno=20 then 1 else 0 end) as deptno_20,
        sum(case when deptno=30 then 1 else 0 end) as deptno_30
    from emp
) emp_cnts,
(select deptno from dept where deptno <= 30) dept

Reverse Pivoting a Result Set into One Column

We want to return all columns from a query as just one column

Sample Output

Use the window function ROW_NUMBER OVER to rank each row based on EMPNO (1–4). Then use a CASE expression to transform three columns into one.

with four_rows (id)
as
(
    select 1
    union all
    select id+1
    from four_rows
    where id < 4
)
 ,
 x_tab (ename,job,sal,rn )
 as
 (
    select e.ename,e.job,e.sal,
     row_number()over(partition by e.empno order by e.empno)
     from emp e
     join four_rows on 1=1
 )

 select
 case rn
     when 1 then ename
     when 2 then job
     when 3 then cast(sal as char(4))
 end emps
 from x_tab

Explanation

Step 1: Use the window function ROW_NUMBER OVER to create a ranking for each employee in DEPTNO 10

Step 2: Add the Cartesian product

Step 3: Use a CASE expression to put ENAME, JOB, and SAL into one column for each employee

Suppressing Repeating Values from a Result Set

We are generating a report, and when two rows have the same value in a column, you want to display that value only once. For example below sample output.

select 
to_number( decode(lag(deptno)over(order by deptno), deptno,null,deptno) ) deptno, 
ename
from emp

Pivoting a Result Set to Facilitate Inter-Row Calculations

We want to make calculations involving data from multiple rows. For that, we want to pivot those rows into columns such that all values we need are then in a single row.

select d20_sal - d10_sal as d20_10_diff,
d20_sal - d30_sal as d20_30_diff
from (
    select sum(case when deptno=10 then sal end) as d10_sal,
    sum(case when deptno=20 then sal end) as d20_sal,
    sum(case when deptno=30 then sal end) as d30_sal
    from emp
) totals_by_dept

Creating Buckets of Data, of a Fixed Size

We want to organize data into evenly sized buckets, with a predetermined number of elements in each bucket. The total number of buckets may be unknown, but we want to ensure that each bucket has five elements.

Sample Output

GRP EMPNO ENAME
--- ---------- -------
1 7369 SMITH
1 7499 ALLEN
1 7521 WARD
1 7566 JONES
1 7654 MARTIN
2 7698 BLAKE
2 7782 CLARK
2 7788 SCOTT
2 7839 KING
2 7844 TURNER
3 7876 ADAMS
3 7900 JAMES
3 7902 FORD
3 7934 MILLER

select 
    ceil(row_number()over(order by empno)/5.0) grp,
    empno,
    ename
from emp

Creating a Predefined Number of Buckets

We want to organize the data into a fixed number of buckets. For example, we want to organize the employees in table EMP into four buckets.

Sample Output

NTILE organizes an ordered set into the number of buckets we specify, with any stragglers distributed into the available buckets starting from the first bucket

select ntile(4)over(order by empno) grp,
empno,
ename
from emp

We want to use SQL to generate histograms that extend horizontally. For example, see below output.

-- Oracle
select 
    deptno,
    lpad('*',count(*),'*') as cnt
from emp
group by deptno

Creating Vertical Histograms

We want to generate a histogram that grows from the bottom up. For example, below output.

select max(deptno_10) d10,
max(deptno_20) d20,
max(deptno_30) d30
from (
    select 
        row_number()over(partition by deptno order by empno) rn,
        case when deptno=10 then '*' else null end deptno_10,
        case when deptno=20 then '*' else null end deptno_20,
        case when deptno=30 then '*' else null end deptno_30
    from emp
) x
group by rn
order by 1 desc, 2 desc, 3 desc

Returning Non-GROUP BY Columns

We are executing a GROUP BY query, and want to return columns in the select list that are not also listed in GROUP BY clause. This is not usually possible, as such ungrouped columns would not represent a single value per row.

We want to find the employees who earn the highest and lowest salaries in each department, as well as the employees who earn the highest and lowest salaries in each job. Also, want to see each employee’s name, the department he works in, his job title, and his salary. Sample output below.

select deptno,
ename,
job,
sal,
case 
    when sal = max_by_dept then 'TOP SAL IN DEPT'
    when sal = min_by_dept then 'LOW SAL IN DEPT'
end dept_status,
case 
    when sal = max_by_job then 'TOP SAL IN JOB'
    when sal = min_by_job then 'LOW SAL IN JOB'
end job_status
from (
    select deptno,ename,job,sal,
    max(sal)over(partition by deptno) max_by_dept,
    max(sal)over(partition by job) max_by_job,
    min(sal)over(partition by deptno) min_by_dept,
    min(sal)over(partition by job) min_by_job
    from emp
) emp_sals
where sal in (max_by_dept,max_by_job,
min_by_dept,min_by_job)

Calculating Simple Subtotals

An example would be a result set that sums the salaries in table EMP by JOB and that also includes the sum of all salaries in table EMP.

A simple subtotal is defined as a result set that contains values from the aggregation of one column along with a grand total value for the table.

Calculating Subtotals for All Possible Expression Combinations

We want to find the sum of all salaries by DEPTNO, and by JOB, for every JOB/ DEPTNO combination.

Sample Output

DEPTNO JOB CATEGORY SAL
------ --------- --------------------- -------
10 CLERK TOTAL BY DEPT AND JOB 1300
10 MANAGER TOTAL BY DEPT AND JOB 2450
10 PRESIDENT TOTAL BY DEPT AND JOB 5000
20 CLERK TOTAL BY DEPT AND JOB 1900
30 CLERK TOTAL BY DEPT AND JOB 950
30 SALESMAN TOTAL BY DEPT AND JOB 5600
30 MANAGER TOTAL BY DEPT AND JOB 2850
20 MANAGER TOTAL BY DEPT AND JOB 2975
20 ANALYST TOTAL BY DEPT AND JOB 6000
    CLERK TOTAL BY JOB 4150
    ANALYST TOTAL BY JOB 6000
    MANAGER TOTAL BY JOB 8275
    PRESIDENT TOTAL BY JOB 5000
    SALESMAN TOTAL BY JOB 5600
10 TOTAL BY DEPT 8750
30 TOTAL BY DEPT 9400
20 TOTAL BY DEPT 10875
GRAND TOTAL FOR TABLE 29025

select 
   deptno,
   job,
   case grouping(deptno)||grouping(job)
      when '00' then 'TOTAL BY DEPT AND JOB'
      when '10' then 'TOTAL BY JOB'
      when '01' then 'TOTAL BY DEPT'
      when '11' then 'GRAND TOTALFOR TABLE'
   end category,
   sum(sal) sal
from emp
group by cube(deptno,job)
order by grouping(job),grouping(deptno)

Identifying Rows That Are Not Subtotals

We have used the CUBE extension of the GROUP BY clause to create a report, and need a way to differentiate between rows that would be generated by a normal GROUP BY clause and those rows that have been generated as a result of using CUBE or ROLLUP.

Sample Output

select deptno, job, sal,
    grouping(deptno) deptno_subtotals,
    grouping(job) job_subtotals
from emp
group by cube(deptno,job)

Using Case Expressions to Flag Rows

We want to return the following result set.

select ename,
    case when job = 'CLERK' then 1 else 0 end as is_clerk,
    case when job = 'SALESMAN' then 1 else 0 end as is_sales,
    case when job = 'MANAGER' then 1 else 0 end as is_mgr,
    case when job = 'ANALYST' then 1 else 0 end as is_analyst,
    case when job = 'PRESIDENT' then 1 else 0 end as is_prez
from emp
order by 2,3,4,5,6

Creating a Sparse Matrix

We want to create a sparse matrix, such as the following one transposing the DEPTNO and JOB columns of table EMP.

select 
    case deptno when 10 then ename end as d10,
    case deptno when 20 then ename end as d20,
    case deptno when 30 then ename end as d30,
    case job when 'CLERK' then ename end as clerks,
    case job when 'MANAGER' then ename end as mgrs,
    case job when 'PRESIDENT' then ename end as prez,
    case job when 'ANALYST' then ename end as anals,
    case job when 'SALESMAN' then ename end as sales
from emp

Grouping Rows by Units of Time

We want to summarize data by some interval of time. For example, we have a transaction log and want to summarize transactions by five-second intervals.

Sample Input

Sample Output

select 
    ceil(trx_id/5.0) as grp,
    min(trx_date) as trx_start,
    max(trx_date) as trx_end,
    sum(trx_cnt) as total
from trx_log
group by ceil(trx_id/5.0)

Performing Aggregations over Different Groups/ Partitions Simultaneously

We want to aggregate over different dimensions at the same time. For example, return a result set that lists each employee’s name, their department, the number of employees in their department (themselves included), the number of employees that have the same job (themselves included in this count as well), and the total number of employees in the EMP table.

select 
    ename,
    deptno,
    count(*) over(partition by deptno) deptno_cnt,
    job,
    count(*) over(partition by job) job_cnt,
    count(*) over() total
from emp

Performing Aggregations over a Moving Range of Values

We want to compute a moving aggregation, such as a moving sum on the salaries in table EMP. We want to compute a sum for every 90 days, starting with the HIREDATE of the first employee.

select hiredate,
sal,
sum(sal)over(order by hiredate range between 90 preceding and current row) spending_pattern
from emp e

Pivoting a Result Set with Subtotals

We want to create a report containing subtotals and then transpose the results to provide a more readable report.

Sample Output

select mgr,
    sum(case deptno when 10 then sal else 0 end) dept10,
    sum(case deptno when 20 then sal else 0 end) dept20,
    sum(case deptno when 30 then sal else 0 end) dept30,
    sum(case flag when '11' then sal else null end) total
from (
    select deptno,mgr,sum(sal) sal,
    cast(grouping(deptno) as char(1))||cast(grouping(mgr) as char(1)) flag
    from emp
    where mgr is not null
    group by rollup(deptno,mgr)
) x
group by mgr