SQL Server - Include NULL using UNPIVOT

Question

UNPIVOT will not return NULLs, but I need them in a comparison query. I am trying to avoid using ISNULL the following example (Because in the real sql there are over 100 fields.:

Select ID, theValue, column_name
From 
(select ID,
  ISNULL(CAST([TheColumnToCompare]  AS VarChar(1000)), '') as TheColumnToCompare
  from MyView
  where The_Date = '04/30/2009'
) MA
UNPIVOT
   (theValue FOR column_name IN 
   ([TheColumnToCompare])
) AS unpvt

Any alternatives?

Answer 1

Cade Roux has the right answer (i'll upvote it)

I'll replace my post with an auditor joke:

Two auditors are in a bank, when armed robbers burst in. While several of the robbers take the money from the tellers, others line the customers, including the auditors, up against a wall, and proceed to take their wallets, watches, etc.

While this is going on auditor number one jams something in auditor number two's hand. Without looking down, auditor number two whispers, "What is this?" to which auditor number one replies, "it's that $50 I owe you."

Answer 2

It's a real pain. You have to switch them out before the UNPIVOT, because there is no row produced for ISNULL() to operate on - code generation is your friend here.

I have the problem on PIVOT as well. Missing rows turn into NULL, which you have to wrap in ISNULL() all the way across the row if missing values are the same as 0.0 for example.

Answer 3

To preserve NULLs, use CROSS JOIN ... CASE:

select a.ID, b.column_name
, column_value = 
    case b.column_name
      when 'col1' then a.col1
      when 'col2' then a.col2
      when 'col3' then a.col3
      when 'col4' then a.col4
    end
from (
  select ID, col1, col2, col3, col4 
  from table1
  ) a
cross join (
  select 'col1' union all
  select 'col2' union all
  select 'col3' union all
  select 'col4'
  ) b (column_name)

Instead of:

select ID, column_name, column_value
From (
  select ID, col1, col2, col3, col4
  from from table1
  ) a
unpivot (
  column_value FOR column_name IN (
    col1, col2, col3, col4)
  ) b

A text editor with column mode makes such queries easier to write. UltraEdit has it, so does Emacs. In Emacs it's called rectangular edit.

You might need to script it for 100 columns.

Answer 4

or, in SQLServer 2008 in shorter way:

... cross join (values('col1'), ('col2'), ('col3'), ('col4')) column_names(column_name)