UNPIVOT operator

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:

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

The result looked as follows:

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

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:

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:

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;

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:

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

Suppose we need to rotate a row containing a NULL value in one of its fields.

WITH utest AS
(SELECT 1 a, 2 b, NULL c)
SELECT * FROM utest;

I.e., we need to transform

Let’s use UNPIVOT:

WITH utest AS
(SELECT 1 a, 2 b, NULL c)
SELECT col, value FROM utest
UNPIVOT (
value FOR col IN (a,b,c)
) AS unpvt;

The first surprise we’re in for is a compile error:

This means the server didn’t implicitly convert the column “c” containing NULL to the type of the first two columns (that can be considered to be of integer type).

Let’s do it explicitly:

WITH utest AS
(SELECT 1 a, 2 b, CAST(NULL AS INT) c)
SELECT col,value FROM utest
UNPIVOT (
value FOR col IN (a,b,c)
) AS unpvt;

Now, here’s the second surprise – as it turns out, UNPIVOT ignores NULL values, and doesn’t include them in the result set.

The first thing that comes to one’s mind is, to replace NULL with some valid value definitely not present in the column. Say, if the subject area doesn’t allow negative values in column c, we can replace NULL with -1:

WITH utest AS
(SELECT 1 a, 2 b,  COALESCE(CAST(NULL AS INT),-1) c)
SELECT col, value FROM utest
UNPIVOT (
value FOR col IN (a, b, c)
) AS unpvt;

 | col | value | |——-|———| | a | 1 | | b | 2 | | c | -1 |

One last step has to be done many people solving exercises at sql-ex.ru tend to forget about. I mean the inverse transformation to NULL. Instead, many users try to figure out a value that would “satisfy” the check system. Sometimes they succeed – say, if comparing NULL with ’’ (an empty string) yields TRUE on the website. However, it’s pretty obvious this isn’t something to rely on. So, here’s the inverse transformation:

WITH utest AS
(SELECT 1 a, 2 b,  COALESCE(CAST(NULL AS INT),-1) c)
SELECT col, NULLIF(value, -1) value FROM utest
UNPIVOT (
value FOR col IN (a, b, c)
) AS unpvt;

The NULLIF function came in very handy in this case.