UNPIVOT operator page 2
Suppose we need to rotate a row containing a NULL value in one of its fields.
I.e., we need to transform
Let’s use UNPIVOT:
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:
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:
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:
The NULLIF function came in very handy in this case.