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
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 ));
- 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 a
sys.odcivarchar2list()collection. This allows the content to be accessed by the macro; macros do not have access to the content of
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 PIVOT
p_PIVOT_COLS: columns that are GROUPed BY explicitly in the input query and pivoted in the PIVOT
p_AGG_COLS: column that are aggregated explicitly in the input query and “re-aggregated” using
MAX() 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) );
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) );
My next post will show how the macro works with multiple group by, pivot and / or aggregation columns.
/* 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
Pretty slick! Thanks as always Stew :)
Hello Stew and Happy New Year 2022 :)
Just two small remarks:
1. Regarding the SQM_UTIL package, the package body fails to compile in LiveSQL, which is now version 126.96.36.199.0,
with the following error:
Errors: PACKAGE BODY SQM_UTIL
Line/Col: 153/5 PL/SQL: Statement ignored
Line/Col: 153/46 PLS-00302: component ‘TABLE_SCHEMA_NAME’ must be declared
Line/Col: 154/5 PL/SQL: Statement ignored
Line/Col: 154/39 PLS-00302: component ‘TABLE_NAME’ must be declared
So, I guess that you are using a later version, in which, very logically, these two missing components were probably added to the DBMS_TF.TABLE_T structure.
However, they are still NOT documented in the 21c documentation … maybe worth reporting to Oracle …
2. Second, regarding the general issue of scalar argument values being “invisible” in a SQL MACRO:
If this invisibility can be worked around by wrapping those values in a collection, or even passing them using COLUMN_T
arguments, as we saw in your 4th post in this seria, then I just wonder why the decision to make them invisible
was taken in the first place ?
In other words, if they were made visible (as usual for any procedure), how/why would this fact impact the general SQL MACRO
functionality, as long as the function would still be called during parsing only ?
Thanks a lot once again for all your wonderful posts and excellent explanations :)
Cheers & Best Regards,
Happy New Year to you and thanks again for your kind words.
1. You are right about two fields “missing” from version 19.8. They have been present for months in 19c Cloud and 21c Cloud. I have complained to Oracle and to Twitter about macro support being different in the Cloud and non-Cloud implementations of 19c. Even when 19c Cloud said it was in version 19.5 it had the same macro functionality as 21c!
By a coincidence, I already added an update to my blog post about SQM_UTIL which says just what you mentioned.
2. I was told that the reason was to avoid SQL injection. I don’t understand that argument, since “injecting” SQL is the whole point of the macro. Also, the implementation is not complete as you point out.