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

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

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

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

sql Server生成错误：

Msg 8167,Level 16,State 1,Line 6

The type of column “Lastname” conflicts with the type of other columns specified in the UNPIVOT list.

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

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

见SQL Fiddle with Demo

在sql Server 2005中引入UNPIVOT之前,我将使用带有UNION ALL的SELECT来取消对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时需要相同长度的逻辑是什么？

解决方法

What is the logic behind requiring the same length when using UNPIVOT?

这个问题可能只对致力于实施UNPIVOT的人员真正负责.您可以在filing a Connect item之前获得此信息.以下是我对推理的理解,可能不是100％准确：

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

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

也就是说,在引入新的语言功能时,没有充分的理由不进行改进,避免过去的错误(显然没有向后兼容的行李).递归公用表表达式(0700 by Andriy M)和UNPIVOT等新功能可以自由地使用相对理智的语义和明确定义的规则.

关于是否包括类型中的长度是否过于明确地打字,将会有一系列的观点,但我个人对此表示欢迎.在我看来,varchar(25)和varchar(50)的类型不一样,只有十进制(8)和十进制(10).在我看来,特殊的套管柱式转换使事情变得更加复杂,并没有增加任何实际价值.

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

如果允许从varchar(25)到varchar(50)的隐式转换,那么它将只是另一个(很可能是隐藏的)隐式转换,具有所有常见的奇怪边缘情况和SET设置灵敏度.为什么不使实现最简单,最明确？ (然而,没有什么是完美的,并且允许在sql_variant中隐藏varchar(25)和varchar(50)是一种遗憾.)

使用APPLY和UNION ALL重写UNPIVOT可以避免(更好)类型行为,因为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的重写与UNPIVOT不完全相同,因为它不会拒绝NULL属性.

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

解决方法

猜你在找的MsSQL相关文章