New sql features

PIVOT and UNPIVOT Operators

Let's look at this task.

Example 1.

Determine the number of models of each type of product for each maker from table Product.

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

Console

Execute

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

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

    Scheme

Below is solution with PIVOT:

Console

Execute

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

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

    Scheme

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

Example 2.

Calculate the average price on laptops depending on screen size.

Task is elementary and solved by the grouping:

Console

Execute

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

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

    Scheme

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

Console

Execute

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

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

    Scheme

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.