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 |