主页 / dba / 问题 / 54353
Accepted
Taryn
Asked: 2013-12-04 06:11:52 +0800 CST

为什么 SQL Server 在使用 UNPIVOT 时要求数据类型长度相同？

  • 772

将UNPIVOT函数应用于未规范化的数据时，SQL Server 要求数据类型和长度相同。我明白为什么数据类型必须相同，但为什么 UNPIVOT 要求长度相同？

假设我有以下需要取消透视的示例数据：

CREATE TABLE People
(
    PersonId int, 
    Firstname varchar(50), 
    Lastname varchar(25)
)

INSERT INTO People VALUES (1, 'Jim', 'Smith');
INSERT INTO People VALUES (2, 'Jane', 'Jones');
INSERT INTO People VALUES (3, 'Bob', 'Unicorn');

如果我尝试 UNPIVOTFirstname和Lastname列类似于：

select PersonId, ColumnName, Value  
from People
unpivot
(
  Value 
  FOR ColumnName in (FirstName, LastName)
) unpiv;

SQL Server 生成错误：

消息 8167，第 16 层，状态 1，第 6 行

“姓氏”列的类型与 UNPIVOT 列表中指定的其他列的类型冲突。

为了解决错误，我们必须使用子查询首先将Lastname列强制转换为具有相同的长度Firstname：

select PersonId, ColumnName, Value  
from
(
  select personid, 
    firstname, 
    cast(lastname as varchar(50)) lastname
  from People
) d
unpivot
(
  Value FOR 
  ColumnName in (FirstName, LastName)
) unpiv;

请参阅带有演示的 SQL Fiddle

在 SQL Server 2005 中引入 UNPIVOT 之前，我将使用SELECTwithUNION ALL来取消透视firstname/lastname列，并且查询将运行而无需将列转换为相同的长度：

select personid, 'firstname' ColumnName, firstname value
from People
union all
select personid, 'LastName', LastName
from People;

请参阅SQL Fiddle with Demo。

我们还能够使用CROSS APPLY在数据类型上没有相同长度的情况下成功地取消透视数据：

select PersonId, columnname, value
from People
cross apply
(
    select 'firstname', firstname union all
    select 'lastname', lastname
) c (columnname, value);

请参阅SQL Fiddle with Demo。

我已通读 MSDN，但没有找到任何解释强制数据类型长度相同的原因。

使用 UNPIVOT 时要求相同长度背后的逻辑是什么？

sql-server unpivot

2 个回答

  1. Best Answer

    使用 UNPIVOT 时要求相同长度背后的逻辑是什么？

    这个问题可能只有致力于实施的人才能真正回答UNPIVOT。您可以通过联系他们寻求支持来获得此信息。以下是我对推理的理解，可能不是100%准确：

    T-SQL 包含任意数量的奇怪语义和其他反直觉行为的实例。其中一些最终将作为弃用周期的一部分消失，但其他一些可能永远不会“改进”或“修复”。除此之外，存在依赖于这些行为的应用程序，因此必须保持向后兼容性。

    隐式转换的规则和表达式类型派生占了上述怪异的很大一部分。我并不羡慕测试人员必须确保SET为新版本保留奇怪的（并且通常是未记录的）行为（在会话值的所有组合下等等）。

    也就是说，在引入新的语言特性（显然没有向后兼容包袱）时，没有充分的理由不进行改进并避免过去的错误。诸如递归公用表表达式之类的新功能（正如Andriy M在评论中提到的那样）并且UNPIVOT可以自由地具有相对健全的语义和明确定义的规则。

    关于在类型中包含长度是否会导致显式输入太过分，会有一系列观点，但我个人对此表示欢迎。在我看来，varchar(25)和varchar(50)的类型并不相同，更多的是decimal(8)和decimal(10)。在我看来，特殊的套管字符串类型转换会使事情变得不必要地复杂化并且没有增加实际价值。

    有人可能会争辩说，只有可能丢失数据的隐式转换才需要明确说明，但也有边缘情况。最终，将需要转换，因此我们不妨明确说明。

    如果允许从varchar(25)to的隐式转换varchar(50)，那将只是另一个（很可能是隐藏的）隐式转换，具有所有通常奇怪的边缘情况和SET设置敏感性。为什么不使实现尽可能简单和最明确呢？（然而，没有什么是完美的，很遗憾允许隐藏在 avarchar(25)中。）varchar(50)sql_variant

    重写UNPIVOTwithAPPLY并UNION ALL避免（更好的）类型行为，因为规则UNION受向后兼容性的影响，并且在联机丛书中被记录为允许不同的类型，只要它们使用隐式转换（数据类型优先级的神秘规则）是可比较的被使用，等等）。

    解决方法包括明确数据类型并在必要时添加显式转换。这对我来说看起来像是进步:)

    编写显式类型的解决方法的一种方法：

    SELECT
    U.PersonId,
    U.ColumnName,
    U.Value
FROM dbo.People AS P
CROSS APPLY
(
    VALUES (CONVERT(varchar(50), Lastname))
) AS CA (Lastname)
UNPIVOT
(
    Value FOR
    ColumnName IN (P.Firstname, CA.Lastname)
) AS U;

    递归 CTE 示例：

    -- Fails
WITH R AS
(
    SELECT Dummy = 'A row'
    UNION ALL
    SELECT 'Another row'
    FROM R
    WHERE Dummy = 'A row'
)
SELECT Dummy
FROM R;

-- Succeeds
WITH R AS
(
    SELECT Dummy = CONVERT(varchar(11), 'A row')
    UNION ALL
    SELECT CONVERT(varchar(11), 'Another row')
    FROM R
    WHERE Dummy = 'A row'
)
SELECT Dummy
FROM R;

    最后，请注意，CROSS APPLY问题中的 rewrite using 与 不太相同UNPIVOT，因为它不拒绝NULL属性。

    • 27

  2. UNPIVOT运营商利用运营IN商。IN 运算符的规范（下面的屏幕截图）表明test_expression（在本例中，位于左侧IN）和每个expression（位于右侧IN）都必须是相同的数据类型。由于相等的传递属性，每个表达式也必须是相同的数据类型。

    在此处输入图像描述

    • 1

相关问题