SQL Server - 使用 UNPIVOT 包含 NULL [英] SQL Server - Include NULL using UNPIVOT

问题描述

UNPIVOT 不会返回 NULLs，但我在比较 query 时需要它们.我试图避免使用 ISNULL 下面的例子(因为在真正的 sql 中有超过 100 个字段):

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

有其他选择吗?

推荐答案

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

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.

我在 PIVOT 上也有问题.缺失的行会变成 NULL，如果缺失值与 0.0 相同，你必须在整个行中将其包裹在 ISNULL() 中例如.

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.

这篇关于SQL Server - 使用 UNPIVOT 包含 NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！