PIVOT operator

Let’s look at this task.

Example 1.

The task can be solved by common way with CASE operator:

SELECT maker,
SUM(CASE type WHEN 'pc' THEN 1 ELSE 0 END) PC
, SUM(CASE type WHEN 'laptop' THEN 1 ELSE 0 END) Laptop
, SUM(CASE type WHEN 'printer' THEN 1 ELSE 0 END) Printer
FROM Product
GROUP BY maker

Below is solution with PIVOT:

SELECT maker, -- column (columns) values which form the row headers
[pc], [laptop], [printer] -- values from the column, which is specified in
-- the ‘type’ clause, forming the column headers
FROM Product -- may be a subquery here
PIVOT -- pivot-table forming
(COUNT(model) -- aggregate function which forms the contents of the pivot table
FOR type -- it will be the column, unique values which will be the column headers
IN([pc], [laptop], [printer]) -- concrete values in a ‘type’ column are specified,
-- which should be used as headers
-- because we cannot take all
) pvt; -- alias of pivot table

I hope that the comments to the code clear enough to write a PIVOT operator without cribs. Let’s try it.

Example 2.

Task is elementary and solved by the grouping:

SELECT screen, AVG(price) avg_
FROM Laptop
GROUP BY screen

And here’s how you can turn the table with PIVOT:

SELECT [avg_],
[11],[12],[14],[15]
FROM (SELECT 'average price' AS 'avg_', screen, price FROM Laptop) x
PIVOT
(AVG(price)
FOR screen
IN([11],[12],[14],[15])
) pvt

Unlike pivot tables, in PIVOT operator, explicit list of columns is required to output. This is a serious restriction, because need to know what kind of data, and therefore we can apply this operator, as a rule, only to lookup tables (or rather, to the data taken from lookup tables).

If the examples studied seem insufficiently to understand and use without difficulties this operator, I will come back to it when I will think up nontrivial examples where PIVOT operator usage allows simplifying a code essentially.

I wrote this opus to help those who having trouble with PIVOT operator. I can agree that in relational language SQL it looks a foreign matter. Indeed, otherwise it cannot be due to rotation (transposition) of the table is not a relational operation, but operation with multidimensional data structures.