08:31

# PIVOT operator

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) PrinterFROM ProductGROUP BY maker`

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 headersFROM Product -- may be a subquery herePIVOT -- pivot-table forming(COUNT(model) -- aggregate function which forms the contents of the pivot tableFOR type -- it will be the column, unique values which will be the column headersIN([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.

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`

screen avg_
11 700.00
12 960.00
14 1175.00
15 1050.00

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`

avg_ 11 12 14 15
average price 700.00 960.00 1175.00 1050.00

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(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.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.