The PIVOT clause does some aggregation, then pivots the result – but it is not dynamic.
Let’s make a deal: you do the aggregation and the JUST_PIVOT table macro will pivot the result, dynamically!
[UPDATE 2022-01-14: the solution I propose here works only in version 19c. For a more complicated solution that works in 19c and 21c, see SQL table macros 11: JUST_PIVOT for 21c (and 19c)]
Doing it the old-fashioned way
Let’s say we want the total salary for each job type in each department. This is a simple aggregation:
select dname, job, sum(sal) sal
from dept
join emp using(deptno)
group by dname, job
order by 1,2
| DNAME | JOB | SAL |
|---|---|---|
| ACCOUNTING | CLERK | 1300 |
| ACCOUNTING | MANAGER | 2450 |
| ACCOUNTING | PRESIDENT | 5000 |
| RESEARCH | ANALYST | 6000 |
| RESEARCH | CLERK | 1900 |
| RESEARCH | MANAGER | 2975 |
| SALES | CLERK | 950 |
| SALES | MANAGER | 2850 |
| SALES | SALESMAN | 5600 |
Now that we know all possible JOB values, we can aggregate and pivot the result directly.
select * from (
select dname, job, sal
from dept
join emp using(deptno)
)
pivot(sum(SAL) SAL for (JOB) in (
('ANALYST') as ANALYST,
('CLERK') as CLERK,
('MANAGER') as MANAGER,
('PRESIDENT') as PRESIDENT,
('SALESMAN') as SALESMAN
));
| DNAME | ANALYST_SAL | CLERK_SAL | MANAGER_SAL | PRESIDENT_SAL | SALESMAN_SAL |
|---|---|---|---|---|---|
| ACCOUNTING | 1300 | 2450 | 5000 | ||
| RESEARCH | 6000 | 1900 | 2975 | ||
| SALES | 950 | 2850 | 5600 |
- I call SAL an “aggregation column” because it is used in an aggregation function.
- JOB is a “pivot column” because its values are used in create the new pivoted columns.
- DNAME is a “group by column” because PIVOT groups by it implicitly.
Using the JUST_PIVOT macro
JUST_PIVOT asks you for four things:
p_INPUT_QUERY: the input query must be a literal wrapped in asys.odcivarchar2list()collection. This allows the content to be accessed by the macro; macros do not have access to the content ofVARCHAR2literals.
Every column in the input query must also appear in one of these column lists:p_GROUP_COLS: columns that are GROUPed BY explicitly in the input query and implicitly in the PIVOTp_PIVOT_COLS: columns that are GROUPed BY explicitly in the input query and pivoted in the PIVOTp_AGG_COLS: column that are aggregated explicitly in the input query and “re-aggregated” usingMAX() in the PIVOT
select * from just_pivot(
p_input_query => sys.odcivarchar2list(
q'§
select dname, job, sum(sal) sal
from dept
join emp using(deptno)
group by dname, job
§'
),
p_group_cols => columns(dname),
p_pivot_cols => columns(job),
p_agg_cols => columns(sal)
);
| DNAME | ANALYST_SAL | CLERK_SAL | MANAGER_SAL | PRESIDENT_SAL | SALESMAN_SAL |
|---|---|---|---|---|---|
| ACCOUNTING | 1300 | 2450 | 5000 | ||
| RESEARCH | 6000 | 1900 | 2975 | ||
| SALES | 950 | 2850 | 5600 |
Advantages of the macro
- The macro actually executes the input query a first time in order to build a complete “pivot-in-list”. We don’t have to.
- The final query is a flashback query, so it is parsed every time and the macro generates an up-to-date final query every time.
- If we generate total values, they are automatically put in the right places in the output!
Here is an example, with one line of code changed :
select * from just_pivot(
p_input_query => sys.odcivarchar2list(
q'§
select dname, job, sum(sal) sal
from dept
join emp using(deptno)
group by cube(dname, job)
§'
),
p_group_cols => columns(dname),
p_pivot_cols => columns(job),
p_agg_cols => columns(sal)
);
| DNAME | ANALYST_SAL | CLERK_SAL | MANAGER_SAL | PRESIDENT_SAL | SALESMAN_SAL | Total_SAL |
|---|---|---|---|---|---|---|
| ACCOUNTING | 1300 | 2450 | 5000 | 8750 | ||
| RESEARCH | 6000 | 1900 | 2975 | 10875 | ||
| SALES | 950 | 2850 | 5600 | 9400 | ||
| Total | 6000 | 4150 | 8275 | 5000 | 5600 | 29025 |
My next post will show how the macro works with multiple group by, pivot and / or aggregation columns.
The code
/* Just Pivot MACRO
This macro takes as input a query that does an explicit GROUP BY.
The macro first uses the input query to get the list of pivot columns,
then wraps the input query in a final query that does a PIVOT using MAX().
The final query has a flashback clause in order to make the cursor unshareable:
- there will be a hard parse phase at every execution,
- the macro will execute and the list of pivot values will be up to date.
Input:
1) p_INPUT_QUERY: the input query must be a literal
wrapped in a sys.odcivarchar2list() collection.
This allows the content to be accessed by the macro;
macros do not have access to the content of VARCHAR2 literals.
Every column in the input query must also appear in one of these column lists:
2) p_GROUP_COLS: columns that are GROUPed BY explicitly in the input query
and implicitly in the PIVOT (*)
3) p_PIVOT_COLS: column that are GROUPed BY explicitly in the input query
and pivoted in the PIVOT (*)
4) p_AGG_COLS: column that are aggregated explicitly in the input query
and "re-aggregated" using MAX() in the PIVOT
5) p_total_label: label to use for pivot totals (if CUBE, ROLLUP, etc.)
null values are assumed to be totals and will be replaced with some non-null label
such as 'Total', which is the default, and will be ordered at the end
Processing:
- within the macro, execute the input query to get the list of pivot columns
- generate the final SQL statement based on the input query and the intermediate result,
adding an AS OF clause to ensure the cursor will not be reused.
*/
create or replace function just_pivot(
p_INPUT_QUERY in sys.odcivarchar2list,
p_GROUP_COLS in dbms_tf.columns_t,
p_PIVOT_COLS in dbms_tf.columns_t,
p_AGG_COLS in dbms_tf.columns_t,
p_total_label in dbms_tf.columns_t default null
) return clob sql_macro is
/*
- PIVOT_IN_LIST concatenates values from all p_PIVOT_COLS columns
for example, if (DNAME,JOB) then
('ACCOUNTING','CLERK') as "ACCOUNTING_CLERK",
('ACCOUNTING','MANAGER') as "ACCOUNTING_MANAGER",
and so on
*/
l_pivot_in_list_sql long :=
q'@select listagg('(' || EXPR || ') as "' || AL || '"', ',
') within group(order by #PIVOT_COLS#)
from (
select distinct
#EXPR# EXPR,
#ALIAS# AL,
#PIVOT_COLS#
from (#INPUT_QUERY#)
)@';
l_PIVOT_COLS varchar2(4000);
l_EXPR varchar2(4000);
l_ALIAS varchar2(4000);
l_final_sql long :=
'with pivoted as (
select * from (
select #NVL_COLS#,
#AGG_COLS#
from (#INPUT_QUERY#)
)
pivot(#AGG_MAX_COLS# for (#PIVOT_COLS#) in (
#PIVOT_IN_LIST#
))
)
select * from pivoted
as of scn dbms_flashback.get_system_change_number
order by #ORDER_COLS#';
l_NVL_COLS varchar2(4000);
l_AGG_COLS varchar2(4000);
l_AGG_MAX_COLS varchar2(4000);
l_PIVOT_IN_LIST varchar2(4000);
l_ORDER_COLS varchar2(4000);
l_total_label varchar2(32) := 'Total';
begin
-- set value of l_total_label, which is needed right away
if p_total_label is not null then
sqm_util.list_columns(p_total_label, l_total_label, '%s', null, true);
end if;
-- set values to be plugged into l_pivot_in_list_sql
sqm_util.list_columns(p_PIVOT_COLS, l_PIVOT_COLS);
sqm_util.list_columns(
p_PIVOT_COLS, l_EXPR,
'''''''''||nvl(%s||null,'''||l_total_label||''')||''''''''',
q'§||','||§',
true
);
sqm_util.list_columns(p_PIVOT_COLS, l_ALIAS,
'nvl(%s||null,'''||l_total_label||''')',
q'§||'_'||§',
true
);
-- plug values into l_pivot_in_list_sql
l_pivot_in_list_sql := replace(replace(replace(replace(
l_pivot_in_list_sql,
'#PIVOT_COLS#', l_PIVOT_COLS),
'#EXPR#', l_EXPR),
'#ALIAS#', l_ALIAS),
'#INPUT_QUERY#', p_INPUT_QUERY(1)
);
dbms_output.put_line('line 109');
-- execute l_pivot_in_list_sql
dbms_output.put_line(l_pivot_in_list_sql);
execute immediate l_pivot_in_list_sql into l_PIVOT_IN_LIST;
dbms_output.put_line(l_PIVOT_IN_LIST);
-- set values to be plugged into l_final_sql
sqm_util.list_columns(
p_GROUP_COLS multiset union p_PIVOT_COLS,
l_NVL_COLS,
'nvl(%s||null, '''||l_total_label||''') %s'
);
sqm_util.list_columns(p_AGG_COLS, l_AGG_MAX_COLS, 'max(%s) %s');
sqm_util.list_columns(p_AGG_COLS, l_AGG_COLS);
sqm_util.list_columns(p_GROUP_COLS, l_ORDER_COLS,
'nullif(%s, '''||l_total_label||''')'
);
-- plug values into l_final_sql
l_final_sql := replace(replace(replace(replace(replace(replace(replace(
l_final_sql,
'#NVL_COLS#', l_NVL_COLS),
'#AGG_COLS#', l_AGG_COLS),
'#INPUT_QUERY#', p_INPUT_QUERY(1)),
'#AGG_MAX_COLS#', l_AGG_MAX_COLS),
'#PIVOT_COLS#', l_PIVOT_COLS),
'#ORDER_COLS#', l_ORDER_COLS),
'#PIVOT_IN_LIST#', l_PIVOT_IN_LIST);
dbms_output.put_line(l_final_sql);
return l_final_sql;
end just_pivot;
/
Posts on SQL table macros
- SQL table macros 1: a moving target?
- SQL table macros 2: “polymorphic” is not “dynamic”
- SQL table macros 3: parameter strangeness
- SQL table macros 4: distribute into equal parts
- SQL table macros 5: dynamic but dangerous!
- SQL table macros 6: SQM_UTIL package
- SQL table macros 7: Select excluding
- SQL table macros 8: Print table
- SQL table macros 9: just pivot!
- SQL table macros 10: JUST_PIVOT examples
- SQL table macros 11: JUST_PIVOT for 21c (and 19c)
- SQL table macros 12: compare tables or queries
- SQL table macros 13: compare with primary key
An Oracle Programmer 