1

I have data with 2 calculated bucket columns I would like to summarize with a PIVOT query. This is an example set of pre-generated data. My bucket columns here are sec and pct.

select '1' id, 'a' cat, 'lt_1_sec' sec, 'lt_25_pct' pct from dual
union all
select '2', 'a' cat, 'lt_1_sec', 'lt_25_pct' from dual
union all
select '3', 'a' cat, 'lt_2_sec', 'lt_25_pct' from dual
union all
select '4', 'a' cat, 'lt_2_sec', 'lt_50_pct' from dual
union all
select '5', 'a' cat, 'lt_2_sec', 'lt_50_pct' from dual
union all
select '6', 'a' cat, 'lt_2_sec', 'lt_75_pct' from dual

The output should look like this

cat lt_1_sec lt_2_sec lt_25_pct lt_50_pct lt_75_pct
a 2 4 3 2 1

I would like to pivot on the values of both sec and pct independently. I am able to use a pivot clause to accomplish this on one or the other, or a permutation of all values of sec by all values of pct, but not both independently and simultaneously.

I have tried multiple pivot clauses, which creates some unexpected behavior, multiple values in the pivot-for clause, which creates a permutation of all values, and multiple aggregation functions, which only performs multiple aggregation options over a single column.

Non-functional example one with multiple pivot clauses (the data returned is incorrect):

with dummy_data as (
                     select cat, sec, pct
                     from (
                            select '1' id, 'a' cat, 'lt_1_sec' sec, 'lt_25_pct' pct
                            from dual
                            union all
                            select '2', 'a' cat, 'lt_1_sec', 'lt_25_pct'
                            from dual
                            union all
                            select '3', 'a' cat, 'lt_2_sec', 'lt_25_pct'
                            from dual
                            union all
                            select '4', 'a' cat, 'lt_2_sec', 'lt_50_pct'
                            from dual
                            union all
                            select '5', 'a' cat, 'lt_2_sec', 'lt_50_pct'
                            from dual
                            union all
                            select '6', 'a' cat, 'lt_2_sec', 'lt_75_pct'
                            from dual
                          )
                   ),
     dummy_pvt as (
                    select *
                    from dummy_data pivot (count(*) for (sec) in ('lt_1_sec' as lt_1_sec,
                      'lt_2_sec' as lt_2_sec)) pivot (count(*) for (pct) in ('lt_25_pct' as lt_25_pct,
                      'lt_50_pct' as lt_50_pct,
                      'lt_75_pct' as lt_75_pct))
                  )
select cat, sum(lt_1_sec), sum(lt_2_sec), sum(lt_25_pct), sum(lt_50_pct), sum(lt_75_pct)
from dummy_pvt
group by cat;
CAT SUM(LT_1_SEC) SUM(LT_2_SEC) SUM(LT_25_PCT) SUM(LT_50_PCT) SUM(LT_75_PCT)
a 2 4 1 1 1

Non-functional example two with multiple pivot-for values (should not product a permutation of all value combinations)

with dummy_data as (
                     select cat, sec, pct
                     from (
                            select '1' id, 'a' cat, 'lt_1_sec' sec, 'lt_25_pct' pct
                            from dual
                            union all
                            select '2', 'a' cat, 'lt_1_sec', 'lt_25_pct'
                            from dual
                            union all
                            select '3', 'a' cat, 'lt_2_sec', 'lt_25_pct'
                            from dual
                            union all
                            select '4', 'a' cat, 'lt_2_sec', 'lt_50_pct'
                            from dual
                            union all
                            select '5', 'a' cat, 'lt_2_sec', 'lt_50_pct'
                            from dual
                            union all
                            select '6', 'a' cat, 'lt_2_sec', 'lt_75_pct'
                            from dual
                          )
                   )
select *
from dummy_data pivot (count(*) for (sec, pct) in (
  ('lt_1_sec', 'lt_25_pct') as lt_1_25, 
  ('lt_1_sec', 'lt_50_pct') as lt_1_50, 
  ('lt_1_sec', 'lt_75_pct') as lt_1_75, 
  ('lt_2_sec', 'lt_25_pct') as lt_2_25,
  ('lt_2_sec', 'lt_50_pct') as lt_2_50,
  ('lt_2_sec', 'lt_75_pct') as lt_2_75));
CAT LT_1_25 LT_1_50 LT_1_75 LT_2_25 LT_2_50 LT_2_75
a 2 0 0 1 2 1

Non-functional example three with multiple aggregate functions (only creates columns for the sec values)

with dummy_data as (
                     select cat, sec, pct
                     from (
                            select '1' id, 'a' cat, 'lt_1_sec' sec, 'lt_25_pct' pct
                            from dual
                            union all
                            select '2', 'a' cat, 'lt_1_sec', 'lt_25_pct'
                            from dual
                            union all
                            select '3', 'a' cat, 'lt_2_sec', 'lt_25_pct'
                            from dual
                            union all
                            select '4', 'a' cat, 'lt_2_sec', 'lt_50_pct'
                            from dual
                            union all
                            select '5', 'a' cat, 'lt_2_sec', 'lt_50_pct'
                            from dual
                            union all
                            select '6', 'a' cat, 'lt_2_sec', 'lt_75_pct'
                            from dual
                          )
                   )
select *
from dummy_data pivot (count(sec) sec_cnt, count(pct) pct_cnt for (sec) in (
  ('lt_1_sec') as lt_1_sec,
  ('lt_2_sec') as lt_2_sec));
CAT LT_1_SEC_SEC_CNT LT_1_SEC_PCT_CNT LT_2_SEC_SEC_CNT LT_2_SEC_PCT_CNT
a 2 2 4 4

Is there really no way to perform multiple pivots over separate sets of values in the same query without permutating the values?

group pivot_1_a pivot_1_b pivot_2_x pivot_2_y pivot_2_z
a cnt_a cnt_b cnt_x cnt_y cnt_z

Where A+B = X+Y+Z = count()

1 Answer 1

0

Same query? yes.

Same pivot? no.

note The table alias goes after the pivot

(You'll also need to remove the duplicate cat column)

with data as (
                            select '1' id, 'a' cat, 'lt_1_sec' sec, 'lt_25_pct' pct
                            from dual
                            union all
                            select '2', 'a' cat, 'lt_1_sec', 'lt_25_pct'
                            from dual
                            union all
                            select '3', 'a' cat, 'lt_2_sec', 'lt_25_pct'
                            from dual
                            union all
                            select '4', 'a' cat, 'lt_2_sec', 'lt_50_pct'
                            from dual
                            union all
                            select '5', 'a' cat, 'lt_2_sec', 'lt_50_pct'
                            from dual
                            union all
                            select '6', 'a' cat, 'lt_2_sec', 'lt_75_pct'
                            from dual
), data_sec as ( select cat, sec from data )
,data_pct as (select cat, pct from data )
select *
from data_sec
  pivot (
    count(sec)
    for (sec) in (
      'lt_1_sec' as lt_1_sec,
      'lt_2_sec' as lt_2_sec
    )
  ) a
  full outer join
    data_pct
      pivot (
        count(pct)
        for (pct) in (
          'lt_25_pct' as lt_25_pct,
          'lt_50_pct' as lt_50_pct,
          'lt_75_pct' as lt_75_pct,
          'lt_100_sec' as lt_100_sec
        )
      ) b
      on a.cat = b.cat
  ;

enter image description here

1
  • Strangely, the code you posted gives me a [1401] ORA-01401: inserted value too large for column. However, if I remove the value for which there is no data (lt_100_sec) or shorten it by one character to lt_100_se then it works fine. I'm on 19c, but possibly hitting a limit on column name length. asktom.oracle.com/pls/apex/…
    – Aaron
    Nov 30, 2023 at 16:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.