SQL Server - Include NULL using UNPIVOT

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    endfrom (  select ID, col1, col2, col3, col4   from table1  ) across 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_valueFrom (  select ID, col1, col2, col3, col4  from table1  ) aunpivot (  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.

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.

I ran 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 UNPIVOTand 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(MeasureType, Measure) SELECT OrderDate, product,employee,Measure,MeasureTypefrom #Orders ordersUNPIVOT   (Measure FOR MeasureType IN       (ItemsCount,GrossAmount))AS unpvt; drop table #Orders