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:

  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:

  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 typeclause, 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. Lets try it.

Example 2.

Calculate the average price on laptops depending on screen size.

Task is elementary and solved by the grouping:

  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:

  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.

Bookmark and Share
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 date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 More tags
The book was updated
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100