loading..
Ðóññêèé    English
17:40
go

PIVOT operator

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
  1. SELECT maker,
  2. SUM(CASE type WHEN 'pc' THEN 1 ELSE 0 END) PC
  3. , SUM(CASE type WHEN 'laptop' THEN 1 ELSE 0 END) Laptop
  4. , SUM(CASE type WHEN 'printer' THEN 1 ELSE 0 END) Printer
  5. FROM Product
  6. GROUP BY maker


Below is solution with PIVOT:

Console
Execute
  1. SELECT maker, -- column (columns) values which form the row headers
  2. [pc], [laptop], [printer] -- values from the column, which is specified in 
  3. -- the ‘type’ clause, forming the column headers
  4. FROM Product -- may be a subquery here
  5. PIVOT -- pivot-table forming
  6. (COUNT(model) -- aggregate function which forms the contents of the pivot table
  7. FOR type -- it will be the column, unique values which will be the column headers
  8. IN([pc], [laptop], [printer]) -- concrete values in a ‘type’ column are specified,
  9. -- which should be used as headers
  10. -- because we cannot take all
  11. ) 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
  1. SELECT screen, AVG(price) avg_ 
  2. FROM Laptop 
  3. 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
  1. SELECT [avg_],
  2.  [11],[12],[14],[15]
  3.  FROM (SELECT 'average price' AS 'avg_', screen, price FROM Laptop) x
  4.  PIVOT
  5.  (AVG(price)
  6.  FOR screen
  7.  IN([11],[12],[14],[15])
  8.  ) 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.


Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.