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
有什么替代方案吗？
为了保留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列编写脚本。
select 'select ''' + column_name + ''' UNION ALL' FROM information_schema.columns WHERE table_name = 'table1' and table_schema = 'dbo' - Anssssss这真是个麻烦事。在执行UNPIVOT之前，您必须将它们替换掉，因为没有产生任何行供ISNULL()操作 - 代码生成在这里是您的朋友。
我在PIVOT上也遇到了这个问题。缺失的行变成NULL，如果缺失值与0.0相同，则必须在整行范围内包装ISNULL()。
我遇到了同样的问题。使用 CROSS APPLY（适用于SQL Server 2005及更高版本）而不是 Unpivot 解决了这个问题。我基于这篇文章找到了解决方案：一种替代（更好的？）方法来 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
或者，以更简短的方式在 SQLServer 2008 中：
...
cross join
(values('col1'), ('col2'), ('col3'), ('col4')) column_names(column_name)
-- 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 | +----------------------+-----------------------+
使用动态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 )
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
好的，空值将被替换为字符串，但是所有行都将被返回！！
使用AWS Redshift进行测试，写于2022年5月。
您可以使用with子句，在其中合并预期为null的列。或者，在UNPIVOT块之前的select语句中使用coalesce。
别忘了使用原始列名别名（不遵循此规则不会导致错误或违反规则，但会节省些喝咖啡的时间）。
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
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”也可以，但可能会更加令未来的编码人员困惑。我相信你已经明白了。