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:

Console

Execute

SELECT screen, AVG(price) avg_ 
FROM Laptop 
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:

Console

Execute

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

Note that the name avg_ can’t 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, let’s 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), let’s convert all other values to it:

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;

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:

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;

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

Pages

PIVOT UNPIVOT

Previous [PIVOT operator]

[PostgreSQL CROSSTAB] Next

SQL Problems and solutions

S. I. Moiseenko

New sql features

PIVOT and UNPIVOT Operators

UNPIVOT operator page 1