loading..
   English
20:14

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

Lets 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 were 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 didnt implicitly convert the column "c" containing NULL to the type of the first two columns (that can be considered to be of integer type).

Lets 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, heres the second surprise as it turns out, UNPIVOT ignores NULL values, and doesnt include them in the result set.

The first thing that comes to ones 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, its pretty obvious this isnt something to rely on. So, heres 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 date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100