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
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:
Console
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.
Calculate the average price on laptops depending on screen size.
Task is elementary and solved by the grouping:
Console
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
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.