SQL Server - 使用 UNPIVOT 包含 NULL

Question

UNPIVOT 不会返回 NULL，但我在比较查询中需要它们。 我试图避免在下面的示例中使用 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

要保留 NULL，请使用 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)

而不是：

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 2

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

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

Answer 3

我遇到了同样的问题。 使用CROSS APPLY（SQL Server 2005 及更高版本）而不是Unpivot 解决了该问题。 我根据这篇文章找到了解决方案 另一种（更好？）方法取消透视
我制作了以下示例来演示 CROSS APPLY 不会忽略像 Unpivot 这样的 NULL。

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

Answer 4

或者，在 SQLServer 2008 中以更短的方式：

...
cross join 
(values('col1'), ('col2'), ('col3'), ('col4')) column_names(column_name)

Answer 5

使用动态 SQL 和 COALESCE，我解决了这样的问题：

DECLARE @SQL NVARCHAR(MAX)
DECLARE @cols NVARCHAR(MAX)
DECLARE @dataCols NVARCHAR(MAX)

SELECT 
    @dataCols = COALESCE(@dataCols + ', ' + 'ISNULL(' + Name + ',0) ' + Name , 'ISNULL(' + Name + ',0) ' + Name )
FROM Metric WITH (NOLOCK)
ORDER BY ID

SELECT 
    @cols = COALESCE(@cols + ', ' + Name , Name )
FROM Metric WITH (NOLOCK)
ORDER BY ID

SET @SQL = 'SELECT ArchiveID, MetricDate, BoxID, GroupID, ID MetricID, MetricName, Value
            FROM 
               (SELECT ArchiveID, [Date] MetricDate, BoxID, GroupID,  ' + @dataCols + '
                FROM MetricData WITH (NOLOCK)
                INNER JOIN Archive WITH (NOLOCK)
                    ON ArchiveID = ID
                WHERE BoxID = ' + CONVERT(VARCHAR(40), @BoxID) + '
                AND GroupID = ' + CONVERT(VARCHAR(40), @GroupID) + ') p
            UNPIVOT
               (Value FOR MetricName IN 
                  (' + @cols + ')
            )AS unpvt
            INNER JOIN Metric WITH (NOLOCK)
                ON MetricName  = Name
            ORDER BY MetricID, MetricDate'

EXECUTE( @SQL )

Answer 6

我发现将 UNPIVOT 结果左外连接到完整的字段列表（可以方便地从 INFORMATION_SCHEMA 中提取）在某些情况下是此问题的实际答案。

-- test data
CREATE TABLE _t1(name varchar(20),object_id varchar(20),principal_id varchar(20),schema_id varchar(20),parent_object_id varchar(20),type varchar(20),type_desc varchar(20),create_date varchar(20),modify_date varchar(20),is_ms_shipped varchar(20),is_published varchar(20),is_schema_published varchar(20))
INSERT INTO _t1 SELECT 'blah1', 3, NULL, 4, 0, 'blah2', 'blah3', '20100402 16:59:23.267', NULL, 1, 0, 0 

-- example
select c.COLUMN_NAME, Value
from INFORMATION_SCHEMA.COLUMNS c
left join (
  select * from _t1
) q1
unpivot (Value for COLUMN_NAME in (name,object_id,principal_id,schema_id,parent_object_id,type,type_desc,create_date,modify_date,is_ms_shipped,is_published,is_schema_published)
) t on t.COLUMN_NAME = c.COLUMN_NAME
where c.TABLE_NAME = '_t1'
</pre>

输出看起来像：

+----------------------+-----------------------+
|    COLUMN_NAME       |        Value          |
+----------------------+-----------------------+
| name                 | blah1                 |
| object_id            | 3                     |
| principal_id         | NULL                  | <======
| schema_id            | 4                     |
| parent_object_id     | 0                     |
| type                 | blah2                 |
| type_desc            | blah3                 |
| create_date          | 20100402 16:59:23.26  |
| modify_date          | NULL                  | <======
| is_ms_shipped        | 1                     |
| is_published         | 0                     |
| is_schema_published  | 0                     |
+----------------------+-----------------------+
    

Answer 7

我遇到了你同样的问题，这是
我的快速而肮脏的解决方案：

您的查询：

 select 
     Month,Name,value 
     from TableName 
 unpivot     
  (        
   Value  for Name in (Col_1,Col_2,Col_3,Col_4,Col_5
  )
) u  
    

替换为：

select Month,Name,value from 
    ( select 
          isnull(Month,'no-data') as Month,
          isnull(Name,'no-data') as Name,
          isnull(value,'no-data') as value from TableName
    ) as T1
unpivot
(
    Value 
    for Name in (Col_1,Col_2,Col_3,Col_4,Col_5)  
) u 

好的，空值将替换为字符串，但将返回所有行！

Answer 8

于 22 年 5 月撰写并在 AWS Redshift 上进行测试。
您可以使用 with 子句来合并需要空值的列。 或者，您可以在 UNPIVOT 块之前的 select 语句中使用合并。

并且不要忘记使用原始列名称别名（不遵循不会破坏或违反规则，但会节省一些喝咖啡的时间）。

Select ID, theValue, column_name
From 
(select ID,
  coalesce(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

或者

WITH TEMP1 as (
select ID,
  coalesce(CAST([TheColumnToCompare]  AS VarChar(1000)), '') as TheColumnToCompare
  from MyView
  where The_Date = '04/30/2009'
)

Select ID, theValue, column_name
From 
(select ID, TheColumnToCompare
  from MyView
  where The_Date = '04/30/2009'
) MA
UNPIVOT
   (theValue FOR column_name IN 
   ([TheColumnToCompare])
) AS unpvt

Answer 9

ISNULL 是答案的一半。 使用 NULLIF 转换回 NULL。 例如，

DECLARE @temp TABLE(
    Foo varchar(50),
    Bar varchar(50) NULL
    );

INSERT INTO @temp( Foo,Bar )VALUES( 'licious',NULL );

SELECT * FROM @temp;

SELECT 
    Col,
    NULLIF( Val,'0Null' ) AS Val 
FROM(
    SELECT
        Foo,
        ISNULL( Bar,'0Null' ) AS Bar
    FROM
        @temp
    ) AS t
UNPIVOT(
    Val FOR Col IN(
        Foo,
        Bar 
        )
    ) up;

这里我使用“0Null”作为我的中间值。 你可以使用任何你喜欢的东西。 但是，如果您选择“Null”等现实世界的内容，则可能会遇到与用户输入发生冲突的风险。 垃圾工作正常“！@#34())0”，但可能会让未来的编码人员更加困惑。 我相信你明白了。