Oracle 11g R1 also introduced the UNPIVOT function, allowing columns to be converted into rows.
ProblemI've created an example that lists cities by row, but two attractions as two columns, with pairing attributes describing the reason for the attraction.
create table aus_attractions(id number, city varchar2(50) , attraction1 varchar2(50) , attraction2 varchar2(50) , reason1 varchar2(50) , reason2 varchar2(50) ); insert into aus_attractions values (1, 'Perth','weather','beaches','sunny','white sand'); insert into aus_attractions values (2, 'Sydney','bridge','blue mountains','climb','scenic'); insert into aus_attractions values (3, 'Melbourne','culture','aussie rules','activities','crowds'); select id, city ,attraction1,attraction2 ,reason1, reason2 from aus_attractions;
|Not optimal relational data design|
I'd like to see each attraction by row - 6 rows instead of 3.
You could solve this with a UNION ALL, and/or a WITH - but one day "they" will ask for 5 options, might as well unpivot.
SolutionThen we can turn our original statement into an inline view, serving the unpivot function.
select id, city, attraction, reason, rec_nbr from ( -- original query: (select id, city ,attraction1,attraction2 ,reason1, reason2 from aus_attractions ) unpivot -- the magic operator ((attraction, reason) -- names of replacement columns for rec_nbr in ( -- new column defining data source in literal alias below -- split each group of fields in here (attraction1, reason1) as 'REC1' ,(attraction2, reason2) as 'REC2' ) ) ); ID CITY ATTRACTION REASON REC_NBR ------ ---------- ----------------- ------------ ------- 1 Perth weather sunny REC1 1 Perth beaches white sand REC2 2 Sydney bridge climb REC1 2 Sydney blue mountains scenic REC2 3 Melbourne culture activities REC1 3 Melbourne aussie rules crowds REC2 6 rows selectedAwesome.
Simple, once you've done it the first time...
DocumentationOracle SQL Language Reference
Oracle Data Warehousing Guide - SQL for Analysis and Reporting
Other great examples of varying depthOTN - Arup Nanda
Oracle-Base - Tim Hall
Oracle FAQ - Unpivot
Oracle-developer.net - Adrian Billington
AMIS - Lucas Jellema
SQL Snippets: Columns to Rows - UNPIVOT (11g)
If you're not already using the above sites for good reference material, you're missing out.
Also check out this example demonstrated at live.oracle.com