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;
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 Server 2005中引入UNPIVOT之前,我将使用带有UNION ALL的SELECT来取消对firstname / lastname列的显示,并且查询将在不需要将列转换为相同长度的情况下运行：
select personid,'firstname' ColumnName,firstname value from People union all select personid,'LastName',LastName from People;
我们还能够使用CROSS APPLY成功地取消数据,而不会在数据类型上具有相同的长度：
select PersonId,columnname,value
from People
cross apply
(
select 'firstname',firstname union all
select 'lastname',lastname
) c (columnname,value);
我已阅读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属性.
