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
