Pivoting tables

PIVOTThe meaning of pivoting a table is to turn n-rows into n-columns.For example given a ales table with a composite primary key of year and quarter you may want to get a result that shows only one row for each year, but a separate column for each quarter.

There a many ways to achieve pivoting, but there is clearly one that is optimal which I will explain here.Pivoting involves two events: First the payload (Results in this case) needs to be dispatch into the Q1 through Q4 columns.This can be done with a CASE expression or DECODE (which is another syntax for CASE):

Note how DECODE injects NULL as an implicit ELSE.The second step is to collapse the rows to get one row per Year.The technique of choice is a GROUP BY here.When grouping on Year we need to use some sort of column function for the columns Q1 through Q4.MAX or MIN() work for most types and they are very cheap. Especially since all but one value will be NULL per group:

UNPIVOTUnpivot is the inverse operation of PIVOT. Here we have several similar columns which are to be combined into one column, but different rows.Let's reuse the same example from above and run it backwards so to speak