UNPIVOT operator page 1

As its name suggests, UNPIVOT performs the opposite operation to PIVOT, presenting data stored in a table row as a single column. In our example from the previous section, we used the PIVOT operator to convert to a row the set of records retrieved by the 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

The result looked as follows:

avg_ 11 12 14 15
average price 700.00 960.00 1175.00 1050.00

We can restore the initial form of the data by applying an unpivot transformation to the pivot query:

  1. SELECT screen -- header of the column that will contain the row headers of the initial table
  2. ,avg__ AS avg_
  3. -- header of the column that will contain the row values of the initial table
  4. FROM(
  5. -- pivot query from the 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. -- end of the pivot query
  15. ) pvt
  17. (avg__
  18. -- header of the column that will contain the row values of the initial table listed below
  19. FOR screen IN([11],[12],[14],[15])
  20. ) unpvt;

Note that the name avg_ cant be used in the UNPIVOT operator, because it is already used in the PIVOT operator; thus, I used a new name avg__ and finally aliased it to fully restore the original data obtained by grouping.

Now, lets go on with a more meaningful example. Suppose the information about flight 1100 has to be presented as follows:

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 here, the data stored in a record of the table is transformed into a column, using the UNPIVOT operator suggests itself. One thing is to be noted though. All values in this column must be of the same type. Since, in our example, values from different columns of the original table are put in this one column, they all have to be converted to a single type. Even more, not just their size (space allocated for storage of the value) should be the same, as well.

For our case, a string type is best suited. Since the columns town_from and town_to already are of type char(25), lets convert all other values to it:

  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: 146, 97

Bookmark and Share
Pages 1 2
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
month ago
(A)-: www.mkm.ru.
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.