错误:列"foo“的类型与UNPIVOT列表中指定的其他列的类型冲突

Question

我取消了一些列的透视，但不幸的是，它一次又一次地返回相同的错误。"BarCode“列的类型为INT，我将其转换为VARCHAR(50)。

谁能告诉我我哪里做错了？

错误如下：

The type of column "BarCode" conflicts with the type of other columns specified in the UNPIVOT list.

SELECT * 
FROM   (SELECT CONVERT(VARCHAR(50), c.[cross-reference type no_]) AS CrossCode, 
               CONVERT(VARCHAR(50), c.[barcode type])             AS BarCode, 
               CONVERT(VARCHAR(50), c.[cross-reference no_])      AS CrossReference, 
               CONVERT(VARCHAR(50), c.[description])              AS CrossDescription 
        FROM   [item cross reference] c) AS pv 
       UNPIVOT (waarde 
               FOR kolom IN (pv.crosscode, 
                             pv.barcode, 
                             pv.crossreference)) AS unpvt 

Answer 1

使用cross apply取消透视：

SELECT v.* 
FROM [item cross reference] icr CROSS APPLY
     (VALUES ('CrossCode', CONVERT(VARCHAR(50), c.[cross-reference type no_]),
             ('BarCode', CONVERT(VARCHAR(50), c.[barcode type]), 
             ('CrossReference', CONVERT(VARCHAR(50), c.[cross-reference no_])), 
             ('CrossDescription', CONVERT(VARCHAR(50), c.[description]))
      ) v(waarde, kolom);

Answer 2

--just remove pv.  :: FOR kolom IN (**pv.**crosscode,

create table [#item cross reference]
(
[cross-reference type no_] varchar(10),
[barcode type] int,
[cross-reference no_] float,
[description] xml
);

insert into [#item cross reference]/*()*/
values('a', 100, 123, '<a>test</a>');

select *
from [#item cross reference];


SELECT * 
FROM   
(
SELECT 
    CONVERT(VARCHAR(50), c.[cross-reference type no_]) AS CrossCode, 
   CONVERT(VARCHAR(50), c.[barcode type])             AS BarCode, 
   CONVERT(VARCHAR(50), c.[cross-reference no_])      AS CrossReference, 
   CONVERT(VARCHAR(50), c.[description])              AS CrossDescription 
FROM   [#item cross reference] c
) AS pv 
UNPIVOT 
(
    waarde FOR kolom IN (CrossCode /*just remove pv.*/, BarCode, CrossReference, CrossDescription)
) AS unpvt;