列类型与unpivot列表中其他列类型冲突

最新推荐文章于 2023-03-10 09:35:20 发布

jiatongtong

最新推荐文章于 2023-03-10 09:35:20 发布

阅读量1w

点赞数 1

分类专栏： SQL SERVER 数据库文章标签： SQL

本文链接：https://blog.csdn.net/xiaojia_boke/article/details/52912927

版权

SQL SERVER 数据库专栏收录该内容

85 篇文章 1 订阅

订阅专栏

导致问题原因可能是：转置列的字段长度不一致问题导致，如下例所示：

具体示例如下：

DECLARE @tmpTable TABLE (
NAME VARCHAR(30)
,address_desc VARCHAR(10)
,personal_desc VARCHAR(100)
)

INSERT INTO @tmpTable(NAME,address_desc,personal_desc)
SELECT 'jim','abc','test1'
UNION
SELECT 'tom','cccc','test2'

SELECT *
FROM @tmpTable --由于原始表中字段长途不一致导致错误
UNPIVOT (
paraval FOR para IN (NAME,address_desc,personal_desc)
) aa

执行结果：

消息 8167，级别 16，状态 1，第 15 行
列 "address_desc" 的类型与 UNPIVOT 列表中指定的其他列的类型冲突。

如果我做如下修改：

DECLARE @tmpTable TABLE (
NAME VARCHAR(30)
,address_desc VARCHAR(10)
,personal_desc VARCHAR(100)
)

INSERT INTO @tmpTable(NAME,address_desc,personal_desc)
SELECT 'jim','abc','test1'
UNION
SELECT 'tom','cccc','test2'

--将所有字段长度改为一致
SELECT *
FROM (
SELECT
CAST(t.NAME AS VARCHAR(100)) AS 'NAME'
,CAST(t.address_desc AS VARCHAR(100)) AS 'address_desc'
,CAST(t.personal_desc AS VARCHAR(100)) AS 'personal_desc'
FROM @tmpTable t
) cc
UNPIVOT (
paraval FOR para IN (NAME,address_desc,personal_desc)
) aa

执行结果：

paraval para
jim NAME
abc address_desc
test1 personal_desc
tom NAME
cccc address_desc
test2 personal_desc

参考网址：

http://dba.stackexchange.com/questions/54353/why-does-sql-server-require-the-datatype-length-to-be-the-same-when-using-unpivo

When applying the UNPIVOT function to data that is not normalized, SQL Server requires that the datatype and length be the same. I understand why the datatype must be the same but why does UNPIVOT require the length to be the same?

Let's say that I have the following sample data that I need to 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');

If I attempt to UNPIVOT the Firstname and Lastname columns similar to:

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

SQL Server generates the error:

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.

In order to resolve the error, we must use a subquery to first cast the Lastname column to have the same length as 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;

See SQL Fiddle with Demo

Prior to UNPIVOT being introduced in SQL Server 2005, I would use a SELECT with UNION ALL to unpivot the firstname/lastname columns and the query would run without the need to convert the columns to the same length:

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

See SQL Fiddle with Demo.

We are also able to successfully unpivot the data using CROSS APPLY without having the same length on the datatype:

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