conflicts with the type of other columns specified in the UNPIVOT list
<p>Can someone help me resolve this issue? I am getting an error saying</p>

<blockquote>
  <p>The type of column "name" conflicts with the type of other columns
  specified in the UNPIVOT list</p>
</blockquote>

<p>I have tried to cast the name to <code>varchar</code>, but nothing worked.</p>

<pre><code>;with hd (id, name, parentid, category)
as
(
  select CAPABILITY_COMPONENT_ID id, CAPABILITY_COMPONENT_NAME name, CAPABILITY_PARENT_ID as parentid, 1 as category
  from [dbo].[TDM_FCT_CPBLITY]
  where CAPABILITY_PARENT_ID = -1
  union all
  select t1.CAPABILITY_COMPONENT_ID, t1.CAPABILITY_COMPONENT_NAME, t1.CAPABILITY_PARENT_ID, hd.category +1
  from [dbo].[TDM_FCT_CPBLITY] t1
  inner join hd
    on t1.CAPABILITY_PARENT_ID = hd.id
)
select category categoryNumber
into #temp
from hd

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + quotename('cat_'+cast(categoryNumber as varchar(10))+'_'+col) 
                  from #temp
                  cross apply (select 'id' col
                               union all 
                               select 'name' col) src
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = ';with hd (id, name, parentid, category)
              as
              (
                select CAPABILITY_COMPONENT_ID as id, CAPABILITY_COMPONENT_NAME as name, CAPABILITY_PARENT_ID as parentid, 1 as category
                from [dbo].[TDM_FCT_CPBLITY]
                where CAPABILITY_PARENT_ID = -1
                union all
                select t1.CAPABILITY_COMPONENT_ID, t1.CAPABILITY_COMPONENT_NAME, t1.CAPABILITY_PARENT_ID, hd.category +1
                from [dbo].[TDM_FCT_CPBLITY] t1
                inner join hd
                  on t1.CAPABILITY_PARENT_ID = hd.id
              ),
              unpiv as
              (
                select value, ''cat_''+cast(category as varchar(5))+''_''+ col col_name
                from
                (
                  select cast(id as varchar(17)) id, name, parentid, category                 
                  from hd
                ) src
                unpivot
                (
                  value for col in (id, name)
                ) un
              )
              select '+@cols+'
              from unpiv
              pivot
              (
                max(value)
                for col_name in ('+@cols+')
               ) piv'

execute(@query)

drop table #temp
</code></pre>