To show the power of the JUST_PIVOT macro, here are several examples. Please refer to:
- SQL table macros 9: just pivot! for the macro code;
- SQL table macros 6: SQM_UTIL package, which has APIs called by the macro.
The examples are based on a minimal dataset which is completely abstract. My apologies for not providing more pleasant data…
drop table t purge;
create table t as
with Col_A as (select 'Val_A'||level Col_A from dual connect by level <= 2),
Col_B as (select 'Val_B'||level Col_B from dual connect by level <= 2),
Col_C as (select 'Val_C'||level Col_C from dual connect by level <= 2),
Col_D as (select 'Val_D'||level Col_D from dual connect by level <= 2)
select * from Col_A,Col_B,Col_C,Col_D;
select * from t order by 1,2,3,4
| COL_A | COL_B | COL_C | COL_D |
|---|---|---|---|
| Val_A1 | Val_B1 | Val_C1 | Val_D1 |
| Val_A1 | Val_B1 | Val_C1 | Val_D2 |
| Val_A1 | Val_B1 | Val_C2 | Val_D1 |
| Val_A1 | Val_B1 | Val_C2 | Val_D2 |
| Val_A1 | Val_B2 | Val_C1 | Val_D1 |
| Val_A1 | Val_B2 | Val_C1 | Val_D2 |
| Val_A1 | Val_B2 | Val_C2 | Val_D1 |
| Val_A1 | Val_B2 | Val_C2 | Val_D2 |
| Val_A2 | Val_B1 | Val_C1 | Val_D1 |
| Val_A2 | Val_B1 | Val_C1 | Val_D2 |
| Val_A2 | Val_B1 | Val_C2 | Val_D1 |
| Val_A2 | Val_B1 | Val_C2 | Val_D2 |
| Val_A2 | Val_B2 | Val_C1 | Val_D1 |
| Val_A2 | Val_B2 | Val_C1 | Val_D2 |
| Val_A2 | Val_B2 | Val_C2 | Val_D1 |
| Val_A2 | Val_B2 | Val_C2 | Val_D2 |
One group column, one pivot column, one aggregation column
This simplest example shows that generated column names are based on the value of the pivot column followed by the name of the aggregated column. The PIVOT clause does this for us.
select * from just_pivot(
p_input_query => sys.odcivarchar2list(
q'§
select Col_A, Col_B, count(*) cnt
from t
group by Col_A, Col_B
§'
),
p_group_cols => columns(Col_A),
p_pivot_cols => columns(Col_B),
p_agg_cols => columns(cnt)
);
Final query generated by the macro
with pivoted as (
select * from (
select nvl("COL_A"||null, 'Total') "COL_A",nvl("COL_B"||null, 'Total') "COL_B",
"CNT"
from (
select Col_A, Col_B, count(*) cnt
from t
group by Col_A, Col_B
)
)
pivot(max("CNT") "CNT" for ("COL_B") in (
('Val_B1') as "Val_B1",
('Val_B2') as "Val_B2"
))
)
select * from pivoted
as of scn dbms_flashback.get_system_change_number
order by nullif("COL_A", 'Total')
| COL_A | Val_B1_CNT | Val_B2_CNT |
|---|---|---|
| Val_A1 | 4 | 4 |
| Val_A2 | 4 | 4 |
Changing one line of code gives us totals by the group column, by the pivot column and a grand total. There is actually a fifth parameter called p_total_label that you can use to change the ‘Total’ label, but whatever the label is, the same one is used everywhere.
select * from just_pivot(
p_input_query => sys.odcivarchar2list(
q'§
select Col_A, Col_B, count(*) cnt
from t
group by cube(Col_A, Col_B)
§'
),
p_group_cols => columns(Col_A),
p_pivot_cols => columns(Col_B),
p_agg_cols => columns(cnt)
);
Final query generated by the macro
with pivoted as (
select * from (
select nvl("COL_A"||null, 'Total') "COL_A",nvl("COL_B"||null, 'Total') "COL_B",
"CNT"
from (
select Col_A, Col_B, count(*) cnt
from t
group by cube(Col_A, Col_B)
)
)
pivot(max("CNT") "CNT" for ("COL_B") in (
('Val_B1') as "Val_B1",
('Val_B2') as "Val_B2",
('Total') as "Total"
))
)
select * from pivoted
as of scn dbms_flashback.get_system_change_number
order by nullif("COL_A", 'Total')
| COL_A | Val_B1_CNT | Val_B2_CNT | Total_CNT |
|---|---|---|---|
| Val_A1 | 4 | 4 | 8 |
| Val_A2 | 4 | 4 | 8 |
| Total | 8 | 8 | 16 |
This example shows how the GROUPING_ID function tells us the GROUP BY level of every “cell” in our output. I’ll use it to eliminate unwanted rows or columns.
select * from just_pivot(
p_input_query => sys.odcivarchar2list(
q'§
select Col_A, Col_B, grouping_id(Col_A, Col_B) gid
from t
group by cube(Col_A, Col_B)
§'
),
p_group_cols => columns(Col_A),
p_pivot_cols => columns(Col_B),
p_agg_cols => columns(gid)
);
| COL_A | Val_B1_GID | Val_B2_GID | Total_GID |
|---|---|---|---|
| Val_A1 | 0 | 0 | 1 |
| Val_A2 | 0 | 0 | 1 |
| Total | 2 | 2 | 3 |
Let’s say I don’t want the Total_CNT column: I simply choose the GROUPING_ID values I want to keep.
select * from just_pivot(
p_input_query => sys.odcivarchar2list(
q'§
select Col_A, Col_B, count(*) cnt
from t
group by cube(Col_A, Col_B)
having grouping_id(Col_A, Col_B) in (0,2)
§'
),
p_group_cols => columns(Col_A),
p_pivot_cols => columns(Col_B),
p_agg_cols => columns(cnt)
);
| COL_A | Val_B1_CNT | Val_B2_CNT |
|---|---|---|
| Val_A1 | 4 | 4 |
| Val_A2 | 4 | 4 |
| Total | 8 | 8 |
Two aggregations
The PIVOT clause allows us to aggregate as many values as we want, of different datatypes if we want.
select * from just_pivot(
p_input_query => sys.odcivarchar2list(
q'§
select Col_A, Col_B, count(*) cnt, 'Count='||count(*) lit
from t
group by cube(Col_A, Col_B)
§'
),
p_group_cols => columns(Col_A),
p_pivot_cols => columns(Col_B),
p_agg_cols => columns(cnt,lit)
);
Final query generated by the macro
with pivoted as (
select * from (
select nvl("COL_A"||null, 'Total') "COL_A",nvl("COL_B"||null, 'Total') "COL_B",
"CNT","LIT"
from (
select Col_A, Col_B, count(*) cnt, 'Count='||count(*) lit
from t
group by cube(Col_A, Col_B)
)
)
pivot(max("CNT") "CNT",max("LIT") "LIT" for ("COL_B") in (
('Val_B1') as "Val_B1",
('Val_B2') as "Val_B2",
('Total') as "Total"
))
)
select * from pivoted
as of scn dbms_flashback.get_system_change_number
order by nullif("COL_A", 'Total')
| COL_A | Val_B1_CNT | Val_B1_LIT | Val_B2_CNT | Val_B2_LIT | Total_CNT | Total_LIT |
|---|---|---|---|---|---|---|
| Val_A1 | 4 | Count=4 | 4 | Count=4 | 8 | Count=8 |
| Val_A2 | 4 | Count=4 | 4 | Count=4 | 8 | Count=8 |
| Total | 8 | Count=8 | 8 | Count=8 | 16 | Count=16 |
Two group-by columns
With two group-by columns, we would normally show subtotals by both columns, totals by the first column, and the grand total. To avoid getting totals by the second column, we can use the GROUPING_ID function just on those two columns.
select * from just_pivot(
p_input_query => sys.odcivarchar2list(
q'§
select Col_A, Col_B, Col_C, count(*) cnt
from t
group by cube(Col_A, Col_B, Col_C)
having grouping_id(Col_A, Col_B) != 2
§'
),
p_group_cols => columns(Col_A, Col_B),
p_pivot_cols => columns(Col_C),
p_agg_cols => columns(cnt)
);
Final query generated by the macro
with pivoted as (
select * from (
select nvl("COL_A"||null, 'Total') "COL_A",nvl("COL_B"||null, 'Total') "COL_B",nvl("COL_C"||null, 'Total') "COL_C",
"CNT"
from (
select Col_A, Col_B, Col_C, count(*) cnt
from t
group by cube(Col_A, Col_B, Col_C)
having grouping_id(Col_A, Col_B) != 2
)
)
pivot(max("CNT") "CNT" for ("COL_C") in (
('Val_C1') as "Val_C1",
('Val_C2') as "Val_C2",
('Total') as "Total"
))
)
select * from pivoted
as of scn dbms_flashback.get_system_change_number
order by nullif("COL_A", 'Total'),nullif("COL_B", 'Total')
| COL_A | COL_B | Val_C1_CNT | Val_C2_CNT | Total_CNT |
|---|---|---|---|---|
| Val_A1 | Val_B1 | 2 | 2 | 4 |
| Val_A1 | Val_B2 | 2 | 2 | 4 |
| Val_A1 | Total | 4 | 4 | 8 |
| Val_A2 | Val_B1 | 2 | 2 | 4 |
| Val_A2 | Val_B2 | 2 | 2 | 4 |
| Val_A2 | Total | 4 | 4 | 8 |
| Total | Total | 8 | 8 | 16 |
Two pivot columns
Again I’ll show subtotals by both columns, totals by the first column, and the grand total. To avoid getting totals by the second column, use the GROUPING_ID function on the two pivot columns.
select * from just_pivot(
p_input_query => sys.odcivarchar2list(
q'§
select Col_A, Col_B, Col_C, count(*) cnt
from t
group by cube(Col_A, Col_B, Col_C)
having grouping_id(Col_B, Col_C) != 2
§'
),
p_group_cols => columns(Col_A),
p_pivot_cols => columns(Col_B, Col_C),
p_agg_cols => columns(cnt),
p_total_label => columns("Tot")
);
Final query generated by the macro
with pivoted as (
select * from (
select nvl("COL_A"||null, 'Tot') "COL_A",nvl("COL_B"||null, 'Tot') "COL_B",nvl("COL_C"||null, 'Tot') "COL_C",
"CNT"
from (
select Col_A, Col_B, Col_C, count(*) cnt
from t
group by cube(Col_A, Col_B, Col_C)
having grouping_id(Col_B, Col_C) != 2
)
)
pivot(max("CNT") "CNT" for ("COL_B","COL_C") in (
('Val_B1','Val_C1') as "Val_B1_Val_C1",
('Val_B1','Val_C2') as "Val_B1_Val_C2",
('Val_B1','Tot') as "Val_B1_Tot",
('Val_B2','Val_C1') as "Val_B2_Val_C1",
('Val_B2','Val_C2') as "Val_B2_Val_C2",
('Val_B2','Tot') as "Val_B2_Tot",
('Tot','Tot') as "Tot_Tot"
))
)
select * from pivoted
as of scn dbms_flashback.get_system_change_number
order by nullif("COL_A", 'Tot')
| COL_A | Val_B1_Val_C1_CNT | Val_B1_Val_C2_CNT | Val_B1_Tot_CNT | Val_B2_Val_C1_CNT | Val_B2_Val_C2_CNT | Val_B2_Tot_CNT | Tot_Tot_CNT |
|---|---|---|---|---|---|---|---|
| Val_A1 | 2 | 2 | 4 | 2 | 2 | 4 | 8 |
| Val_A2 | 2 | 2 | 4 | 2 | 2 | 4 | 8 |
| Tot | 4 | 4 | 8 | 4 | 4 | 8 | 16 |
Two group-by columns and two pivot columns
Again I’ll show subtotals, totals and the grand total. To avoid getting totals by the second column, use the GROUPING_ID function on the two group-by columns and the two pivot columns.
select * from just_pivot(
p_input_query => sys.odcivarchar2list(
q'§
select Col_A, Col_B, Col_C, Col_D, count(*) cnt
from t
group by cube(Col_A, Col_B, Col_C, Col_D)
having 2 not in (
grouping_id(Col_A, Col_B),
grouping_id(Col_C, Col_D)
)
§'
),
p_group_cols => columns(Col_A, Col_B),
p_pivot_cols => columns(Col_C, Col_D),
p_agg_cols => columns(cnt),
p_total_label => columns("Tot")
);
Final query generated by the macro
with pivoted as (
select * from (
select nvl("COL_A"||null, 'Tot') "COL_A",nvl("COL_B"||null, 'Tot') "COL_B",nvl("COL_C"||null, 'Tot') "COL_C",nvl("COL_D"||null, 'Tot') "COL_D",
"CNT"
from (
select Col_A, Col_B, Col_C, Col_D, count(*) cnt
from t
group by cube(Col_A, Col_B, Col_C, Col_D)
having 2 not in (
grouping_id(Col_A, Col_B),
grouping_id(Col_C, Col_D)
)
)
)
pivot(max("CNT") "CNT" for ("COL_C","COL_D") in (
('Val_C1','Val_D1') as "Val_C1_Val_D1",
('Val_C1','Val_D2') as "Val_C1_Val_D2",
('Val_C1','Tot') as "Val_C1_Tot",
('Val_C2','Val_D1') as "Val_C2_Val_D1",
('Val_C2','Val_D2') as "Val_C2_Val_D2",
('Val_C2','Tot') as "Val_C2_Tot",
('Tot','Tot') as "Tot_Tot"
))
)
select * from pivoted
as of scn dbms_flashback.get_system_change_number
order by nullif("COL_A", 'Tot'),nullif("COL_B", 'Tot')
| COL_A | COL_B | Val_C1_Val_D1_CNT | Val_C1_Val_D2_CNT | Val_C1_Tot_CNT | Val_C2_Val_D1_CNT | Val_C2_Val_D2_CNT | Val_C2_Tot_CNT | Tot_Tot_CNT |
|---|---|---|---|---|---|---|---|---|
| Val_A1 | Val_B1 | 1 | 1 | 2 | 1 | 1 | 2 | 4 |
| Val_A1 | Val_B2 | 1 | 1 | 2 | 1 | 1 | 2 | 4 |
| Val_A1 | Tot | 2 | 2 | 4 | 2 | 2 | 4 | 8 |
| Val_A2 | Val_B1 | 1 | 1 | 2 | 1 | 1 | 2 | 4 |
| Val_A2 | Val_B2 | 1 | 1 | 2 | 1 | 1 | 2 | 4 |
| Val_A2 | Tot | 2 | 2 | 4 | 2 | 2 | 4 | 8 |
| Tot | Tot | 4 | 4 | 8 | 4 | 4 | 8 | 16 |
This function allows any combination of one or more group-by columns, pivot columns and aggregations, limited only by screen real estate and how much horizontal scrolling you want to inflict on the users. In my next post I’ll talk about some limitations.
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 