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

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 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.