SQL Server -使用UNPIVOT包含NULL

Question

UNPIVOT不会返回NULL%s，但我需要在比较query中使用它们。在下面的示例中，我尽量避免使用ISNULL (因为在真正的sql中有100多个字段)：

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

有其他选择吗？

Answer 1

这是一个真正的痛苦。您必须在UNPIVOT之前将它们切换出来，因为没有为ISNULL()生成可操作的行-代码生成是您的好朋友。

我在PIVOT上也遇到了这个问题。缺少的行将转换为NULL，如果缺少的值与0.0相同，则必须在整个行中将其包装在ISNULL()中。

Answer 2

要保留NULL，请使用CROSS JOIN ...案例：

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)

而不是：

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

具有列模式的文本编辑器使这样的查询更容易编写。UltraEdit有它，Emacs也有。在Emacs中，它被称为矩形编辑。

您可能需要为100列编写脚本。

Answer 3

我遇到了同样的问题。使用CROSS APPLY (SQL Server2005及更高版本)而不是Unpivot解决了这个问题。我找到了基于这篇文章的解决方案，An Alternative (Better?) Method to UNPIVOT，我做了下面的例子来证明交叉应用不会像Unpivot一样忽略NULLs。

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,MeasureType
from #Orders orders
UNPIVOT
   (Measure FOR MeasureType IN 
      (ItemsCount,GrossAmount)
)AS unpvt;


 drop table #Orders