"Date" conflicts with the type of other columns specified in the UNPIVOT list SQL server

Question

I'm doing unpivot like this:

select    
--ID,
 Value
 from dbo.[staging]
 unpivot
 (
    Value
  for col in ([ Code]
  , [Date]
  ,[ Registered Name]
  ,[Entity Name]
  ,[transactions]
  ,[ PID])

 ) un

I'm getting error:

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

Note: My staging table has date column with type datetime.

How can I reslove this error? Pl help.

Answer 1

Normally you would include additional columns in the unpivot, such as the identifier. But the solution to your problem is to use a subquery to cast the column to a varchar():

select --ID, Value
from (select s.*, convert(varchar(255), [date], 121) as datestr
      from dbo.[staging]
     ) t
unpivot (Value for col in ([ Code], datestr, [ Registered Name], [Entity Name], [transactions], [ PID]
                          )
        ) un;