collation issue with unpivot query

Question

I am getting this error message:

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

SELECT fi_contactid, 
       question, 
       answer 
FROM   (SELECT soi.fi_contactid, 
               soi.statuscode, 
               fi_description = soi.fi_description COLLATE database_default, 
               soi.fi_elevationissuecode, 
               soi.fi_identifiedby 
        FROM   ficrm_mscrm.dbo.fi_soiissuebase AS soi WITH (nolock)) p 
       UNPIVOT (question 
               FOR answer IN (statuscode, 
                              fi_description, 
                              fi_elevationissuecode, 
                              fi_identifiedby)) AS unpt 

fi_contactid is a uniqueidentifier
fi_description is nvarchar(255)
fi_elevationissuecode is int
fi_identifieby is uniqueidentifier

Is there something I am doing wrong with collation within the unpivot statement?

Answer 1

UNPIVOT will assume the first column in the IN list is the datatype of the FOR field, and will attempt a direct cast to that type, which may or may not work.

You need to either change the order of your IN clause (if a direct cast will work) or cast each column as the same type in your sub query.

Here is any example of a solution:

SELECT fi_contactid, 
       question, 
       answer 
FROM   (SELECT soi.fi_contactid, 
               CONVERT(VARCHAR(255), soi.statuscode) statuscode, 
               fi_description = soi.fi_description COLLATE database_default , 
               CONVERT(VARCHAR(255), soi.fi_elevationissuecode) fi_elevationissuecode, 
               CONVERT(VARCHAR(255), soi.fi_identifiedby) fi_identifiedby
        FROM   ficrm_mscrm.dbo.fi_soiissuebase AS soi WITH (nolock)) p 
       UNPIVOT (question 
               FOR answer IN (statuscode, 
                              fi_description, 
                              fi_elevationissuecode, 
                              fi_identifiedby)) AS unpt