Here's an example pivot query.
select bldg_num, bldg_name, category, total_area
where category in ('Office', 'Common Space', 'Classroom', 'Conference Room')
) pivot (
-- sum, max, min, count, etc are optional
-- this is where you pivot row values into columns
for category in (
'Office' as OFFICE,
'Common Space' as COMMON,
'Classroom' as CLASSROOM,
'Conference Room' as CONF_ROOM
Hope that helps some.
1 person found this helpful
A flat out PIVOT or UNPIVOT won't work.
You need to transpose the data.
Here is some pretty good info on PIVOT/UNPIVOT
You can transpose the data by unpivoting it and then pivoting it:
with data(Region, INV_VOL, ADJ_VOL, CM_VOL) as ( select 'EMEA', 100,200,300 from dual union all select 'JAPAC',300,400,600 from dual union all select 'LAD', 400,100,80 from dual union all select 'NAMER',200,400,800 from dual ), unpivot_data as ( select * from data unpivot ( vol -- Column Name used to hold the Values of data for vol_id -- Column Name to used to hold the Original Column Name in ( INV_VOL, ADJ_VOL, CM_VOL ) ) ) select * from unpivot_data pivot ( min(vol) for Region in ('EMEA' as "EMEA",'JAPAC' AS "JAPAC",'LAD' AS "LAD",'NAMER' AS "NAMER") )
Interesting distinction on the word "transpose", which leaves the title of my recent blog post a little wanting.
I can't say I've ever need to do both. Perhaps with some table/data samples, we can re-write the query to be a little more 'normal' ?