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

I Run into the same problem, Using CROSS APPLY (Sql Server 2005 and Later) Instead of Unpivot Solved the problem. I found the solution Based on this Article An Alternative (Better?) Method to UNPIVOT and i made the following example to demonstrate that CROSS APPLY will NOT Ignore Nulls like Unpivot .

create table #Orders (OrderDate datetime, product nvarchar(100), ItemsCount float,GrossAmount float, employee nvarchar(100))

 insert into #Orders
 select getutcdate(),'Windows',10,10.32,'Me'
 union 
 select getutcdate(),'Office',31,21.23,'you'
 union 
 select getutcdate(),'Office',31,55.45,'me'
 union  
 select getutcdate(),'Windows',10,null,'You'

SELECT OrderDate, product,employee,Measure,MeasureType
 from #Orders orders
 CROSS APPLY (
    VALUES ('ItemsCount',ItemsCount),('GrossAmount',GrossAmount)
    ) 
    x(Measure, MeasureType) 


SELECT OrderDate, product,employee,Measure,MeasureType
from #Orders orders
UNPIVOT
   (Measure FOR MeasureType IN 
      (ItemsCount,GrossAmount)
)AS unpvt;


 drop table #Orders