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属性.

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!