Column conflicts with the type of other columns in the unpivot list

Question

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

Answer 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).

Answer 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; 

Answer 3

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.

Answer 4

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.