I have need to unpivot a table, with fields of varying data types, arranged like this:

PersonalReference   FirstName   LastName
----------------------------------------
123456              John        Smith  

This is to easily compare differences with another dataset which is arranged like this:

PersonalReference   DataDesc   Value
----------------------------------------
123456              FirstName  Jon
123456              LastName   Smith

With the eventual goal being a dataset arranged like this:

PersonalReference   DataDesc   System1Value   System2Value   NoMatch
--------------------------------------------------------------------
123456              FirstName  John            John          1

This code, I think, is syntactically correct:

select
    [PersonalReference],
    [DataDesc],
    [Value]
from
(
    select
        [PersonalReference],
        [Forename],
        [Surname],
        [JoiningDate],
        [LeavingDate],
        [ContractualHours],
        [Position],
        [EmploymentType],
        [Location],
        [PersonalRef_Manager],
        [FullName_Manager],
        [PersonalRef_ClinicalManager],
        [FullName_ClinicalManager],
        [PersonalRef_SeniorManager],
        [FullName_SeniorManager],
        [EmailAddress],
        [UserName],
        [PositionReference],
        [ContractType]
    from MyData
) as [p]
unpivot
(
    [Value] for [DataDesc] in
    (
        [Forename],
        [Surname],
        [JoiningDate],
        [LeavingDate],
        [ContractualHours],
        [Position],
        [EmploymentType],
        [Location],
        [PersonalRef_Manager],
        [FullName_Manager],
        [PersonalRef_ClinicalManager],
        [FullName_ClinicalManager],
        [PersonalRef_SeniorManager],
        [FullName_SeniorManager],
        [EmailAddress],
        [UserName],
        [PositionReference],
        [ContractType]
    )
) as unpvt

But gives me this error:

The type of column “JoiningDate” conflicts with the type of other columns specified in the UNPIVOT list.

I’ve tried converting the date and numeric fields in the unpivot list, but this presents the same error:

select
    [PersonalReference],
    [DataDesc],
    [Value]
from
(
    select
        try_convert(nvarchar(50), [PersonalReference]) as [PersonalReference],
        [Forename],
        [Surname],
        try_convert(nvarchar(50), [JoiningDate]) as [JoiningDate],
        try_convert(nvarchar(50), [LeavingDate]) as [LeavingDate],
        try_convert(nvarchar(50), [ContractualHours]) as [ContractualHours],
        [Position],
        [EmploymentType],
        [Location],
        [PersonalRef_Manager],
        [FullName_Manager],
        [PersonalRef_ClinicalManager],
        [FullName_ClinicalManager],
        [PersonalRef_SeniorManager],
        [FullName_SeniorManager],
        [EmailAddress],
        [UserName],
        [PositionReference],
        [ContractType]
    from MyData
) as [p]
unpivot
(
    [Value] for [DataDesc] in
    (
        [Forename],
        [Surname],
        [JoiningDate],
        [LeavingDate],
        [ContractualHours],
        [Position],
        [EmploymentType],
        [Location],
        [PersonalRef_Manager],
        [FullName_Manager],
        [PersonalRef_ClinicalManager],
        [FullName_ClinicalManager],
        [PersonalRef_SeniorManager],
        [FullName_SeniorManager],
        [EmailAddress],
        [UserName],
        [PositionReference],
        [ContractType]
    )
) as unpvt

Short of putting the data into a # table to change all the fields to nvarchar and using that table instead, which I’d rather avoid, I’m a bit stuck on what to try next.

Any help our pointers would be appreciated.