I really like Oracle 11g database and one of my favourite features is the PIVOT/UNPIVOT clause in SQL. PIVOT is used to present rows as columns and UNPIVOT is the opposite operation, columns are presented as rows. I have found them very useful for APEX applications, when I need to present data in table rows as columns in an APEX form for user input.
PIVOT feature seems to have recieved a lot of bloggers attention, but I haven't read much about UNPIVOT. Using UNPIVOT with one column is pretty straight forward and easy, just a small example (I use Oracle Database 220.127.116.11 Enterprise Edition for all the examples here):
Fill it up with test data:
Now, if you want to select JAN, FEB, MAR, APR columns as separate rows, then its possible to use the simplest UNPIVOT clause:
Now, lets take a more interesting case. What if some of the columns are combined into logical groups and you want to preserve them in one row? For example:
For every month there is income and expense column.
UNPIVOT allows to create column groups, like this: