Search code examples
sqlpivothierarchyunpivot

SQL Hierarchy data Pivot-Unpivot - column type conflict error


I am trying to get the hierarchy data in rows into columns. I am getting the following error "The type of column "lvlCode" conflicts with the type of other columns specified in the UNPIVOT list."

My query is as follows

;with hd (orgid, lvlCode,description, parentorgid, category)
as
(
  select orgid, lvlCode,description, parentorgid, 1 as category
  from orgunit
  where parentorgid is null
  union all
  select t1.orgid, t1.lvlCode,t1.description, t1.parentorgid, hd.category +1
  from orgunit t1
  inner join hd
    on t1.parentorgid = hd.orgid
),
unpiv as
(
  select value, 'cat_'+cast(category as varchar(5))+'_'+ col col_name
  from
  (
    select cast(orgid as varchar(17)) orgid, lvlCode,description, parentorgid, category
    from hd
  ) src
  unpivot
  (
    value for col in (orgid, lvlCode,description)
  ) un
)
select [cat_1_orgid], [cat_1_lvlCode],[cat_1_description],
                   [cat_2_orgid], [cat_2_lvlCode],[cat_2_description],
                   [cat_3_orgid], [cat_3_lvlCode],[cat_3_description],
                   [cat_4_orgid], [cat_4_lvlCode],[cat_4_description],
                   [cat_5_orgid], [cat_5_lvlCode],[cat_5_description],
                   [cat_6_orgid], [cat_6_lvlCode],[cat_6_description],
                   [cat_7_orgid], [cat_7_lvlCode],[cat_7_description]
from unpiv
pivot
(
  max(value)
  for col_name in ([cat_1_orgid], [cat_1_lvlCode],[cat_1_description],
                   [cat_2_orgid], [cat_2_lvlCode],[cat_2_description],
                   [cat_3_orgid], [cat_3_lvlCode],[cat_3_description],
                   [cat_4_orgid], [cat_4_lvlCode],[cat_4_description],
                   [cat_5_orgid], [cat_5_lvlCode],[cat_5_description],
                   [cat_6_orgid], [cat_6_lvlCode],[cat_6_description],
                   [cat_7_orgid], [cat_7_lvlCode],[cat_7_description])
) piv

Solution

  • try to convert lvlCode into varchar data type

    ...
       (
        select CAST(orgid as varchar(17)) orgid, 
               CAST(lvlCode as varchar(17)) lvlCode, 
               description, parentorgid, category
        from hd
        )
    ...