19:43

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

UNPIVOT operator page 2

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

Console

Execute

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

I.e., we need to transform

a	b	c
1	2	NULL


a	1
b	2
c	NULL

Let’s use UNPIVOT:

Console

Execute

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:

The type of column "c" conflicts with the type of other columns specified in the UNPIVOT list.

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:

Console

Execute

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;

col	value
a	1
b	2

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:

Console

Execute

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:

Console

Execute

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;

col	value
a	1
b	2
c	NULL

The NULLIF function came in very handy in this case.

Pages

COALESCE NULL NULLIF UNPIVOT

Previous [PIVOT operator]

[PostgreSQL CROSSTAB] Next

Last added:

Tags

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
several days ago

SQL Problems and solutions

S. I. Moiseenko

New sql features

PIVOT and UNPIVOT Operators

UNPIVOT operator page 2