Column conflicts with the type of other columns in

问题:

Im pivoting sys.[views] into key value pairs to compare with values on another server for consistency testing. Im running into an issue which returns the error.

Msg 8167, Level 16, State 1, Line 51

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

Query:

SELECT
sourceUnpivoted.idServer,
sourceUnpivoted.sourceServerName,
sourceUnpivoted.name,
sourceUnpivoted.columnName,
sourceUnpivoted.columnValue
FROM (
SELECT 
CAST('1' AS VARCHAR(255)) AS idServer,
CAST('thisOne' AS VARCHAR(255)) AS sourceServerName,
CAST('theDatabase' AS VARCHAR(255)) AS sourceDatabaseName,
CAST(name AS VARCHAR(255)) AS name,
CAST(object_id AS VARCHAR(255)) AS object_id,
CAST(principal_id AS VARCHAR(255)) AS principal_id,
CAST(schema_id AS VARCHAR(255)) AS schema_id,
CAST(parent_object_id AS VARCHAR(255)) AS parent_object_id,
CAST(type AS VARCHAR(255)) AS type,
CAST(type_desc AS VARCHAR(255)) AS type_desc,
CAST(create_date AS VARCHAR(255)) AS create_date,
CAST(lock_escalation_desc AS VARCHAR(255)) AS lock_escalation_desc
...
FROM noc_test.dbo.stage_sysTables
) AS databaseTables
UNPIVOT (
columnValue FOR columnName IN (
object_id,
principal_id,
schema_id,
parent_object_id,
type,
type_desc,
create_date,
lock_escalation_desc
) 
) AS sourceUnpivoted

Why does this not like [type],[type_desc],[lock_escalation_desc] ??? Ive also tried CONVERT(VARCHAR(255),type) AS type

回答1:

It's actually a collation issue. I can resolve it by changing these lines:

CAST([type] collate database_default AS VARCHAR(255)) AS [type], 
CAST(type_desc collate database_default AS VARCHAR(255)) AS type_desc, 
CAST(create_date AS VARCHAR(255)) AS create_date, 
CAST(lock_escalation_desc collate database_default AS VARCHAR(255)) AS lock_escalation_desc 

The specific issue is that name is collated as Latin1_General_CI_AS, whereas the other 3 columns you mentioned are collated as Latin1_General_CI_AS_KS_WS (At least, on my machine, I'm not sure what it would be like on a server/database with different default collation).

回答2:

This is one of the solution for this type error

1: create the this table

CREATE TABLE People
(
PersonId int, 
Firstname varchar(50), 
Lastname varchar(25)
)

2: Then insert

INSERT INTO People VALUES (1, 'Jim', 'Smith');
INSERT INTO People VALUES (2, 'Jane', 'Jones');
INSERT INTO People VALUES (3, 'Bob', 'Unicorn');

3: run this script you get the error

Msg 8167, Level 16, State 1, Line 3 The type of column "Lastname" conflicts with the type of other columns specified in the UNPIVOT list.

SELECT PersonId, ColumnName, Value
FROM People
unpivot(Value FOR ColumnName IN (FirstName, LastName)) unpiv;

4: the solution is you 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;

回答3:

The PIVOT/UNPIVOT clause is sensitive to the ANSI Padding Status of the column (right-click -> properties in SSMS) as well as the type, size and collation. Try specifying SET ANSI_PADDING ON|OFF in the session before adding or recreating the column in question so it matches the others in the PIVOT/UNPIVOT clause.

回答4:

Ran into this same error and I just made all the columns in the table of the same data type - I had a mix of int, varchar, nvarchar of various lengths. Once I converted all the columns in my table to the same type - nvarchar(255) it worked perfectly.

回答5:

I had the same issue. Fixed it by right clicking on the column header and selecting change type "using locale". See attached screen shot

1: