loading..
Ðóññêèé    English
19:28

UNPIVOT operator page 2

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

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

I.e., we need to transform

a    b    c
1    2    NULL
to

a    1
b    2
c    NULL

Let’s use UNPIVOT:

Console
Execute
  1. WITH utest AS
  2. (SELECT 1 a, 2 b, NULL c)
  3. SELECT col, value FROM utest
  4. UNPIVOT (
  5. value FOR col IN (a,b,c)
  6. ) 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
  1. WITH utest AS
  2. (SELECT 1 a, 2 b, CAST(NULL AS INT) c)
  3. SELECT col,value FROM utest
  4. UNPIVOT (
  5. value FOR col IN (a,b,c)
  6. ) 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
  1. WITH utest AS
  2. (SELECT 1 a, 2 b,  COALESCE(CAST(NULL AS INT),-1) c)
  3. SELECT col, value FROM utest
  4. UNPIVOT (
  5. value FOR col IN (a, b, c)
  6. ) 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
  1. WITH utest AS
  2. (SELECT 1 a, 2 b,  COALESCE(CAST(NULL AS INT),-1) c)
  3. SELECT col, NULLIF(value, -1) value FROM utest
  4. UNPIVOT (
  5. value FOR col IN (a, b, c)
  6. ) AS unpvt;

col    value
a    1
b    2
c    NULL

The NULLIF function came in very handy in this case.


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