Oracle Pivot, Can it be Dynamic?

It's usually quite simple to make a PIVOT clause dynamically determine in which columns to place the values. The number of columns (or pivot groups) needs to be known. I haven't yet seen a way to dynamically determine how many columns will be generated by a query.

First of all, here's a quick example of a regular PIVOT. Note that the pivot columns are all hard-coded into the query. "Abe" doesn't show up because his name wasn't in the "IN" list.

/* --- code --- */

   WITH 
     w_data  as
       ( select  2009 yr,  'Bob' nm,  72 score  from dual union all
         select  2010   ,  'Bob'   ,  84        from dual union all
         select  2011   ,  'Bob'   ,  61        from dual union all
         select  2009   ,  'Abe'   ,  27        from dual union all
         select  2009   ,  'Liz'   ,  94        from dual union all
         select  2010   ,  'Liz'   ,  93        from dual union all
         select  2011   ,  'Liz'   ,  81        from dual union all
         select  2010   ,  'Jan'   ,  93        from dual union all
         select  2011   ,  'Jan'   ,  98        from dual 
       )
   select  *
     from  w_data
    PIVOT  ( avg(score)  as av
           FOR  nm  IN
             ( 'Bob' as bob
             , 'Liz' as liz
             , 'Jan' as jan
             )
           )
   ;


/* --- Results --- */

     --  YR     BOB_AV   LIZ_AV   JAN_AV
     --  -----  -------  -------  -------
     --  2009   72       94
     --  2010   84       93       93
     --  2011   61       81       98

The trick is to give each value, on which we want to pivot, it's own unique rank. An analytic function will do this quite nicely. We'll call this 'prepared' data our "Pre-Pivot" data.

/* --- code --- */

   WITH 
     w_data  as
       ( select  2009 yr,  'Bob' nm,  72 score  from dual union all
         select  2010   ,  'Bob'   ,  84        from dual union all
         select  2011   ,  'Bob'   ,  61        from dual union all
         select  2009   ,  'Abe'   ,  27        from dual union all
         select  2009   ,  'Liz'   ,  94        from dual union all
         select  2010   ,  'Liz'   ,  93        from dual union all
         select  2011   ,  'Liz'   ,  81        from dual union all
         select  2010   ,  'Jan'   ,  93        from dual union all
         select  2011   ,  'Jan'   ,  98        from dual 
       )
   , w_pre_pivt  as
       ( select  yr
              ,  nm
              ,  dense_rank() over (order by nm) rnk
              ,  score
           from  w_data
       )
   select  *
     from  w_pre_pivt ;


/* --- Results --- */

     --  YR     NM    RNK   SCORE
     --  -----  ----  ----  ------
     --  2009   Abe   1     27
     --  2010   Bob   2     84
     --  2011   Bob   2     61
     --  2009   Bob   2     72
     --  2010   Jan   3     93
     --  2011   Jan   3     98
     --  2011   Liz   4     81
     --  2010   Liz   4     93
     --  2009   Liz   4     94

Now we'll do a standard PIVOT on the rank column that we just generated in the Pre-Pivot data. In this example we've chosen 5 columns -- if there are more than 5 names, we would only see the top 5 -- if there are less than 5 names in our data then we will see blank columns. We can code our query to create as many or as few columns as we need. Note that we also include the name as one of the measures -- this is so we can identify which pivot-group values belong to which name.

/* --- code --- */

   WITH 
     w_data  as
       ( select  2009 yr,  'Bob' nm,  72 score  from dual union all
         select  2010   ,  'Bob'   ,  84        from dual union all
         select  2011   ,  'Bob'   ,  61        from dual union all
         select  2009   ,  'Abe'   ,  27        from dual union all
         select  2009   ,  'Liz'   ,  94        from dual union all
         select  2010   ,  'Liz'   ,  93        from dual union all
         select  2011   ,  'Liz'   ,  81        from dual union all
         select  2010   ,  'Jan'   ,  93        from dual union all
         select  2011   ,  'Jan'   ,  98        from dual 
       )
   , w_pre_pivt  as
       ( select  yr
              ,  nm
              ,  dense_rank() over (order by nm) rnk
              ,  score
           from  w_data
       )
   select  *
     from  w_pre_pivt
    PIVOT  ( max(nm)     as nm  
           , avg(score)  as av
           FOR  rnk  IN ( 1, 2, 3, 4, 5 )
           )
   ;


/* --- Results --- */

     --  YR     1_NM   1_AV   2_NM   2_AV   3_NM   3_AV   4_NM   4_AV   5_NM   5_AV
     --  -----  -----  -----  -----  -----  -----  -----  -----  -----  -----  -----
     --  2009   Abe    27     Bob    72                   Liz    94
     --  2010                 Bob    84     Jan    93     Liz    93
     --  2011                 Bob    61     Jan    98     Liz    81

And now, if we would prefer to format the output a little we can create a heading row and "union-all" it to the data. Note that the numeric values now need to be converted to character since we are tacking the name values together with the average value measures.

/* --- code --- */

   WITH 
     w_data  as
       ( select  2009 yr,  'Bob' nm,  72 score  from dual union all
         select  2010   ,  'Bob'   ,  84        from dual union all
         select  2011   ,  'Bob'   ,  61        from dual union all
         select  2009   ,  'Abe'   ,  27        from dual union all
         select  2009   ,  'Liz'   ,  94        from dual union all
         select  2010   ,  'Liz'   ,  93        from dual union all
         select  2011   ,  'Liz'   ,  81        from dual union all
         select  2010   ,  'Jan'   ,  93        from dual union all
         select  2011   ,  'Jan'   ,  98        from dual 
       )
   , w_pre_pivt  as
       ( select  yr
              ,  nm
              ,  dense_rank() over (order by nm) rnk
              ,  score
           from  w_data
       )
   , w_pivoted  as
       ( select  *
           from  w_pre_pivt
          PIVOT  ( max(nm)     as nm  
                 , avg(score)  as av
                 FOR  rnk  IN ( 1, 2, 3, 4, 5 )
                 )
       )
   select  NULL            as "Year/Name"
        ,  max( "1_NM" )   as "Prsn-1"
        ,  max( "2_NM" )   as "Prsn-2"
        ,  max( "3_NM" )   as "Prsn-3"
        ,  max( "4_NM" )   as "Prsn-4"
        ,  max( "5_NM" )   as "Prsn-5"
     from  w_pivoted
   UNION ALL
   select  yr
        ,  to_char( "1_AV" ) 
        ,  to_char( "2_AV" ) 
        ,  to_char( "3_AV" ) 
        ,  to_char( "4_AV" ) 
        ,  to_char( "5_AV" ) 
     from  w_pivoted
    order  by 1 nulls first
   ;


/* --- Results --- */

     --  Year/Name   Prsn-1   Prsn-2   Prsn-3   Prsn-4   Prsn-5
     --  ----------  -------  -------  -------  -------  -------
     --              Abe      Bob      Jan      Liz
     --  2009        27       72                94
     --  2010                 84       93       93
     --  2011                 61       98       81