New sql features

PIVOT and UNPIVOT Operators

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:

Console

Execute

SELECT screen, AVG(price) avg_ 
FROM Laptop 
GROUP BY screen;

SELECT screen, AVG(price) avg_ FROM Laptop GROUP BY screen;

    Scheme

As result, has received the following representation:

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

Console

Execute

SELECT screen -- column header, which will contain the row headers from source table
,avg__ AS avg_
-- column header, which will contain the row values from source table 
FROM(
-- pivot-query from previous example
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
-- the end of pivot-query
 ) pvt
 UNPIVOT
 (avg__ 
-- column header, which will contain the row values from source table listed below 
 FOR screen IN([11],[12],[14],[15])
 ) unpvt;

SELECT screen -- column header, which will contain the row headers from source table ,avg__ AS avg_ -- column header, which will contain the row values from source table from( -- pivot-query from previous example 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 -- the end of pivot-query ) pvt UNPIVOT (avg__ -- column header, which will contain the row values from source table listed below FOR screen in([11],[12],[14],[15]) ) unpvt;

    Scheme

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:

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:

Console

Execute

SELECT trip_no,CAST(id_comp AS CHAR(25)) id_comp, 
CAST(plane AS CHAR(25)) plane,town_from,town_to, 
CONVERT(CHAR(25),time_out, 108) time_out, 
CONVERT(CHAR(25),time_in,108) time_in 
FROM Trip 
WHERE trip_no =1100;

SELECT trip_no,CAST(id_comp AS CHAR(25)) id_comp, CAST(plane AS CHAR(25)) plane,town_from,town_to, CONVERT(CHAR(25),time_out, 108) time_out, CONVERT(CHAR(25),time_in,108) time_in FROM Trip WHERE trip_no =1100;

    Scheme

No scheme

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

CONVERT(CHAR(25),time_out, 108)

The rest, I hope, clear from the code:

Console

Execute

SELECT trip_no, spec, info FROM ( 
SELECT trip_no,CAST(id_comp AS CHAR(25)) id_comp,
CAST(plane AS CHAR(25)) plane,
CAST(town_from AS CHAR(25)) town_from,
CAST(town_to AS CHAR(25)) town_to, 
CONVERT(CHAR(25),time_out, 108) time_out, 
 CONVERT(CHAR(25),time_in,108) time_in 
FROM Trip 
WHERE trip_no =1100 ) x 
UNPIVOT( info 
FOR spec IN(id_comp, plane, town_from, town_to, time_out, time_in) 
) unpvt;

SELECT trip_no, spec, info FROM ( SELECT trip_no,CAST(id_comp AS CHAR(25)) id_comp, CAST(plane AS CHAR(25)) plane, CAST(town_from AS CHAR(25)) town_from, CAST(town_to AS CHAR(25)) town_to, CONVERT(CHAR(25),time_out, 108) time_out, CONVERT(CHAR(25),time_in,108) time_in FROM Trip WHERE trip_no =1100 ) x UNPIVOT( info FOR spec IN(id_comp, plane, town_from, town_to, time_out, time_in) ) unpvt;

    Scheme

No scheme

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