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

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');

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

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;

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

#1 楼

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


这个问题可能只有致力于实施UNPIVOT的人们才能真正回答。您可以通过与他们联系以获得支持来获取此信息。以下是我对推理的理解，这可能不是100％准确的：T-SQL包含任意数量的怪异语义和其他违反直觉的行为实例。其中一些最终将作为弃用周期的一部分消失，但其他一些可能永远不会“改进”或“固定”。除了这些以外，还存在依赖于这些行为的应用程序，因此必须保留向后兼容性。

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

在引入新的语言功能时（没有明显的向后兼容性），没有充分的理由不做改进，避免过去的错误。递归公用表表达式（如Andriy M在评论中提到的）和UNPIVOT等新功能可以自由使用相对理智的语义和明确定义的规则。

关于是否是否包括类型中的长度在内的显式键入实在太过分了，但是我个人很欢迎它。在我看来，varchar(25)和varchar(50)的类型不相同，仅decimal(8)和decimal(10)的类型不同。在我看来，特殊的大写字符串类型转换使不必要的事情变得复杂，并且没有任何实际价值。

有人可能会争辩说，只应明确声明可能会丢失数据的隐式转换，但是那里也有一些极端情况。最终，将需要进行一次转换，因此我们也可以将其明确化。

如果允许从varchar(25)到varchar(50)的隐式转换，则它将是另一个（最可能是隐藏的）隐式转换。 ，以及所有常见的怪异边缘情况和SET设置灵敏度。为什么不使实现最简单，最明确呢？ （但是，没有什么是完美的，可惜的是不允许将varchar(25)和varchar(50)隐藏在sql_variant内。）

用UNPIVOT和APPLY重写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示例：
CROSS APPLY进行的重写与UNPIVOT不太相同，因为它不会拒绝NULL属性。

#2 楼

UNPIVOT运算符使用IN运算符。 IN运算符的规范（如下图所示）表明test_expression（在这种情况下，位于IN的左侧）和每个expression（在IN的右侧）都必须是相同的数据类型。由于相等性的传递属性，每个表达式也必须具有相同的数据类型。