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
