Previously I
had written about grouping output of queries. There is a special case of such
grouping available via the PIVOT function. At a high level, this function changes
rows of output to columns. There are three elements at play that are selected
in a CTE when you do a PIVOT. These are the grouping, spreading, and
aggregation elements.
The grouping
element is one for rows. The spreading element is one for columns. And the
aggregation element is at the intersection of the other two. The spreading and
aggregation element must be columns of a table.
The PIVOT
takes a single aggregate function applied on the aggregate element. The FOR
clause is done on a spreading element. The IN clause contains values from the
spreading element column. The grouping elements is what is left over in the
SELECT statement that is not either a spreading or aggregation element.
There is also an UNPIVOT function which can
reverse the PIVOT function. Columns get turned into rows. The results are in
table format. It will automatically filter out NULL values. UNPIVOT is not a
full undo of a PIVOT. You can only recover values that can be reconstructed.
