This topic demonstrates how to collect and spread values from multiple rows under a single column, like those in VAL below
GROUP_KEY PIVOT_KEY VAL ---------- ---------- ---------- G0 P0 a G0 P0 b G0 P0 (null) G0 P1 A G0 P1 BB G0 P1 CCC G0 P2 1 G0 P2 2222 G0 (null) xyz G0 (null) (null) G1 P0 a G1 P0 b G1 P0 c G1 P2 1 G1 P2 2 (null) P0 c (null) P1 C (null) (null) (null)
across an unknown number of columns, rows, and groups like this.
GROUP_KEY P0 P1 P2 UNASSIGNED --------- -- --- ---- ---------- G0 a A 1 xyz G0 b BB 2222 G0 CCC G1 a 1 G1 b 2 G1 c (null) c C
Unlike the Transposing M:1 topic, here we assume we do not know, in advance, what the PIVOT_KEY values will be or how many distinct PIVOT_KEY values will be encountered in the base table. However, we will assume we know the maximum possible number of distinct PIVOT_KEY values (we will use 5 in our examples).
Solution
In the following code we will see a static query that appears to be dynamic. I.e. it appears to return a variable number of columns with variable column headings depending on the data selected. The effect is achieved by using certain SQL*Plus features to hide unused columns. As an added bonus the technique allows us to dynamically size each column to fit the largest value that appears under it.
Now let's look at the first part of the solution which does some setup work. Its output is not normally displayed.
set feedback off
column c1_print new_value v1_print noprint
column c2_print new_value v2_print noprint
column c3_print new_value v3_print noprint
column c4_print new_value v4_print noprint
column c5_print new_value v5_print noprint
column c1_heading new_value v1_heading noprint
column c2_heading new_value v2_heading noprint
column c3_heading new_value v3_heading noprint
column c4_heading new_value v4_heading noprint
column c5_heading new_value v5_heading noprint
column c1_format new_value v1_format noprint
column c2_format new_value v2_format noprint
column c3_format new_value v3_format noprint
column c4_format new_value v4_format noprint
column c5_format new_value v5_format noprint
select
max( decode( c_num, 1, c_print, 'noprint' ) ) as c1_print ,
max( decode( c_num, 2, c_print, 'noprint' ) ) as c2_print ,
max( decode( c_num, 3, c_print, 'noprint' ) ) as c3_print ,
max( decode( c_num, 4, c_print, 'noprint' ) ) as c4_print ,
max( decode( c_num, 5, c_print, 'noprint' ) ) as c5_print
,
max( decode( c_num, 1, c_heading, null ) ) as c1_heading ,
max( decode( c_num, 2, c_heading, null ) ) as c2_heading ,
max( decode( c_num, 3, c_heading, null ) ) as c3_heading ,
max( decode( c_num, 4, c_heading, null ) ) as c4_heading ,
max( decode( c_num, 5, c_heading, null ) ) as c5_heading
,
max( decode( c_num, 1, c_format, null ) ) as c1_format ,
max( decode( c_num, 2, c_format, null ) ) as c2_format ,
max( decode( c_num, 3, c_format, null ) ) as c3_format ,
max( decode( c_num, 4, c_format, null ) ) as c4_format ,
max( decode( c_num, 5, c_format, null ) ) as c5_format
from
(
select
row_number() over ( order by pivot_key ) as c_num ,
'print' as c_print ,
nvl(pivot_key, 'UNASSIGNED' ) as c_heading ,
'a' ||
greatest
( max( nvl(length(nvl(pivot_key, 'UNASSIGNED' )),0) ),
max( nvl(length(val),0) )
)
as c_format
from t3
where regexp_like( val, '[abcABC]' ) -- Note 1
group by pivot_key
)
;
column c1 &v1_print heading &v1_heading format &v1_format
column c2 &v2_print heading &v2_heading format &v2_format
column c3 &v3_print heading &v3_heading format &v3_format
column c4 &v4_print heading &v4_heading format &v4_format
column c5 &v5_print heading &v5_heading format &v5_format
Note 1: This line is not part of the solution. It simply simulates a typical WHERE clause.
The next step displays the final result. Columns for PIVOT_KEY's that are not in the result set will not be displayed.
column group_key null '(null)'
select
group_key ,
max( decode( pivot_num, 1, val, null ) ) as C1 ,
max( decode( pivot_num, 2, val, null ) ) as C2 ,
max( decode( pivot_num, 3, val, null ) ) as C3 ,
max( decode( pivot_num, 4, val, null ) ) as C4 ,
max( decode( pivot_num, 5, val, null ) ) as C5
from
( select
group_key ,
row_number()
over( partition by group_key, pivot_key order by val )
as row_num ,
dense_rank() over ( order by pivot_key ) as pivot_num ,
val
from t3
where regexp_like( val, '[abcABC]' )
)
group by group_key, row_num
order by group_key, row_num
;
GROUP_KEY P0 P1 ---------- -- --- G0 a A G0 b BB G0 CCC G1 a G1 b G1 c (null) c C
The last query returned two PIVOT_KEY columns. Let's see the same query return four columns (we remove the WHERE clause discussed in Note 1 above).
set feedback off
select
max( decode( c_num, 1, c_print, 'noprint' ) ) as c1_print ,
max( decode( c_num, 2, c_print, 'noprint' ) ) as c2_print ,
max( decode( c_num, 3, c_print, 'noprint' ) ) as c3_print ,
max( decode( c_num, 4, c_print, 'noprint' ) ) as c4_print ,
max( decode( c_num, 5, c_print, 'noprint' ) ) as c5_print
,
max( decode( c_num, 1, c_heading, null ) ) as c1_heading ,
max( decode( c_num, 2, c_heading, null ) ) as c2_heading ,
max( decode( c_num, 3, c_heading, null ) ) as c3_heading ,
max( decode( c_num, 4, c_heading, null ) ) as c4_heading ,
max( decode( c_num, 5, c_heading, null ) ) as c5_heading
,
max( decode( c_num, 1, c_format, null ) ) as c1_format ,
max( decode( c_num, 2, c_format, null ) ) as c2_format ,
max( decode( c_num, 3, c_format, null ) ) as c3_format ,
max( decode( c_num, 4, c_format, null ) ) as c4_format ,
max( decode( c_num, 5, c_format, null ) ) as c5_format
from
(
select
row_number() over ( order by pivot_key ) as c_num ,
'print' as c_print ,
nvl(pivot_key, 'UNASSIGNED' ) as c_heading ,
'a' ||
greatest
( max( nvl(length(nvl(pivot_key, 'UNASSIGNED' )),0) ),
max( nvl(length(val),0) )
)
as c_format
from t3
group by pivot_key
)
;
column c1 &v1_print heading &v1_heading format &v1_format
column c2 &v2_print heading &v2_heading format &v2_format
column c3 &v3_print heading &v3_heading format &v3_format
column c4 &v4_print heading &v4_heading format &v4_format
column c5 &v5_print heading &v5_heading format &v5_format
select
group_key ,
max( decode( pivot_num, 1, val, null ) ) as C1 ,
max( decode( pivot_num, 2, val, null ) ) as C2 ,
max( decode( pivot_num, 3, val, null ) ) as C3 ,
max( decode( pivot_num, 4, val, null ) ) as C4 ,
max( decode( pivot_num, 5, val, null ) ) as C5
from
( select
group_key ,
row_number()
over( partition by group_key, pivot_key order by val )
as row_num ,
dense_rank() over ( order by pivot_key ) as pivot_num ,
val
from t3
)
group by group_key, row_num
order by group_key, row_num
;
GROUP_KEY P0 P1 P2 UNASSIGNED ---------- -- --- ---- ---------- G0 a A 1 xyz G0 b BB 2222 G0 CCC G1 a 1 G1 b 2 G1 c (null) c C
Note how the widths of the P0 and UNASSIGNED columns fit the column heading whereas the widths of the P1 and P2 columns fit the largest data value.
Other Solutions
While the solution above has the advantage of being a pure SQL solution (no custom PL/SQL modules are required) it does rely on certain features available only with SQL*Plus. Solutions which do not rely on SQL*Plus can be found at the following links.
- Ask Tom - "pivot a result set"
- Ask Tom - "pivot a result set" - Just cheating
- OTN SQL and PL/SQL Forum: Re: Crosstab query using pure SQL only
Tags
| Dynamic Columns |
| Dynamic Number of Columns |
| Dynamic Column Headings |
| Dynamic Column Width or Format |
| Unknown Columns |
| Unknown Number of Columns |