Column Conflicts with the Type of Other Columns in the Unpivot List

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

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

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

This post was very helpful to explain the issue. Basically, I had to convert the values to decimal for all the columns in the inner select statement of the unpivot section:

Error : The type of column "DOB" conflicts with the type of other columns specified in the UNPIVOT list

The type of column conflicts with the type of other columns specified in the UNPIVOT list - SQL Server 2008

You need to convert all three columns to the same type:

SELECT MUESTRAS, POZOS, PRIORITY, NAME, VALUE
FROM (SELECT MUESTRAS, POZOS, PRIORITY,
             CAST(VAR1 AS NVARCHAR(MAX)) AS VAR1,
             CAST(NUM1 AS NVARCHAR(MAX)) as NUM1,
             CAST(NUM2 AS NVARCHAR(MAX)) as NUM2
      FROM WS_LEYES_PTXT_PILAS_CALCULADAS
     ) p
UNPIVOT
   (VALUE FOR NAME IN
      (VAR1,-- Varchar maybe Null
       NUM1,-- Float
       NUM2)-- Float
) AS unpvt;

You might want to use something like str() for more control over the format.

Error : The type of column DOB conflicts with the type of other columns specified in the UNPIVOT list

As the result will bring back all columns in rows, building a new derived column with all the values, you must ensure, that the types fit together.

You can wrap all your columns in CAST

SELECT
  ColumnName,
  value
FROM (SELECT
  CAST(id AS NVARCHAR(MAX)) [ID],
  CAST(firstname AS NVARCHAR(MAX)) [First Name],
  CAST(lastname AS NVARCHAR(MAX)) [Last Name],
  CAST(dob AS NVARCHAR(MAX)) [DOB],
  CAST(sex AS NVARCHAR(MAX)) [Gender]
FROM client
WHERE id = '11') d
UNPIVOT
(
Value FOR
ColumnName IN ([ID], [First Name], [Last Name], [DOB], [Gender])
) unpiv;

The DOB will be converted to the default setting of your machine. Using CONVERT you might enforce a given date/time format.

SQL Server - Conflicts column type during UNPIVOT

Based on the fact that it works from some databases and not from others, the likeliest culprit is collation.

When you cast a varchar/nvarchar column to varchar/nvarchar, it will maintain its existing collation. When you cast another type like int (like the port column), it takes the default collation of the database from which you're executing the query. If the vendor database has a different collation than msdb, then the unpivot will give the error you're seeing.

A couple possible fixes:

1. Add this before your query to force it to execute from msdb:

   USE msdb;
   

2. Manually force your port column to use the same collation as msdb:

   --Check the collation for msdb
   SELECT name,collation_name FROM master.sys.databases
   --Use that collation (ex: SQL_Latin1_General_CP1_CI_AS) in your query
   CAST(s.port AS VARCHAR(256)) COLLATE SQL_Latin1_General_CP1_CI_AS port
   

3. Force the other columns to use the same default collation that port is getting:

   CAST(p.name AS VARCHAR(256)) COLLATE DATABASE_DEFAULT name, 
   CAST(a.email_address AS VARCHAR(256)) COLLATE DATABASE_DEFAULT email_address,
   ...
   

---

Related Topics