Русский    English

UNPIVOT operator

As the name of the operator, UNPIVOT performs the reverse of the PIVOT operator, i.e. represents the data stored in the table row in one column. In example, considered in previous section, we converted table into string using PIVOT operator, the table was obtained using following query:

  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

As result, has received the following representation:

avg_ 11 12 14 15
average price 700.00 960.00 1175.00 1050.00

Initial results we can get, if applied to the pivot-query unpivot-transformation:

  1. SELECT screen -- column header, which will contain the row headers from source table
  2. ,avg__ AS avg_
  3. -- column header, which will contain the row values from source table
  4. FROM(
  5. -- pivot-query from previous example
  6. SELECT [avg_],
  7. [11],[12],[14],[15]
  8. FROM (SELECT 'average price' AS 'avg_', screen, price FROM Laptop) x
  9. PIVOT
  10. (AVG(price)
  11. FOR screen
  12. IN([11],[12],[14],[15])
  13. ) pvt
  14. -- the end of pivot-query
  15. ) pvt
  17. (avg__
  18. -- column header, which will contain the row values from source table listed below
  19. FOR screen IN([11],[12],[14],[15])
  20. ) unpvt;

Note that the name avg_ can not be used in the UNPIVOT operator, because it is already used in PIVOT operator, so I used new name avg__, which would then assign an alias to fully reconstruct the result obtained with grouping.

Let us now consider a more substantial example. Suppose you want to display information on 1100 trip in the following form:

trip_no spec info
1100 id_comp 4
1100 plane Boeing
1100 town_from Rostov
1100 town_to Paris
1100 time_out 14:30:00
1100 time_in 17:50:00

Since the information from the row of the table is transformed into a column, it begs the use of the operator UNPIVOT. It should make one remark. The values in this column should be the same type. As in this column in our example, the values are collected from different columns of the source table; you need to convert them to a single type. Moreover, not only the types must match but and size.

Common type in our case is a string type. Since the columns town_from and town_to already have type char (25), then convert all to this type:

  1. SELECT trip_no,CAST(id_comp AS CHAR(25)) id_comp,
  2. CAST(plane AS CHAR(25)) plane,town_from,town_to,
  3. CONVERT(CHAR(25),time_out, 108) time_out,
  4. CONVERT(CHAR(25),time_in,108) time_in
  5. FROM Trip
  6. WHERE trip_no =1100;

trip_no id_comp plane town_from town_to time_out time_in
1100 4 Boeing Rostov Paris 14:30:00 17:50:00

Here we are at the same time transformed the time of departure / arrival, removed the part of date:

  1. CONVERT(CHAR(25),time_out, 108)

The rest, I hope, clear from the code:

  1. SELECT trip_no, spec, info FROM (
  2. SELECT trip_no,CAST(id_comp AS CHAR(25)) id_comp,
  3. CAST(plane AS CHAR(25)) plane,
  4. CAST(town_from AS CHAR(25)) town_from,
  5. CAST(town_to AS CHAR(25)) town_to,
  6. CONVERT(CHAR(25),time_out, 108) time_out,
  7. CONVERT(CHAR(25),time_in,108) time_in
  8. FROM Trip
  9. WHERE trip_no =1100 ) x
  10. UNPIVOT( info
  11. FOR spec IN(id_comp, plane, town_from, town_to, time_out, time_in)
  12. ) unpvt;

Column with the name spec is used to display the names of parameters, and column info contains parameters themselves. The result of the query already has been presented in the task statement.

Suggested exercises: 41, 97

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 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 exercise 32 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100