Assistance with PIVOT function

I have a reference table that’s currently delimited with ‘-‘ that I’m needing to split the values out into multiple columns. Our version of SQL DB doesn’t support the string_split function.

The first part of the script (multiple CTE) is returning the results into multiple rows, which I’m then wanting to pivot into columns.

Is someone able to please assist with the PIVOT portion (or even a new statement if it achieves the same result). Am looking to have the results returned per final table format?

Thanks

Original Data:

declare @T table (ID int, Col varchar(100), description varchar(50))
insert into @T values (1, 'MV-RUC-DEBT-ASSESS', 'MV Debt Assessment')
;

  with cte as (
  select    a.ID 
            ,replace(a.Col,'-', ' ') as "Col"
            , a.description
        from @T a
  ),

  cte2 as (
  select    a.ID
            , n.r.value('.', 'varchar(50)') "Value"
            , a.description
              from cte a
  cross apply (select cast('<r>'+replace(replace(Col,'&','&amp;'), ' ', '</r><r>')+'</r>' as xml)) as S(XMLCol)
  cross apply S.XMLCol.nodes('r') as n(r)
  
  )

  select *
  from cte2 a
  pivot 
 (max(value) for a.ID in ([1], [2], [3], [4])) as "Pivot"

I’m expecting the results to look like

>Solution :

When you use a.ID as part of the pivot, it has only ones so that you only would get the max value of value.

adding a row_number would give you the wanted result

declare @T table (ID int, Col varchar(100), description varchar(50))
insert into @T values (1, 'MV-RUC-DEBT-ASSESS', 'MV Debt Assessment')
;

  with cte as (
  select    a.ID 
            ,replace(a.Col,'-', ' ') as "Col"
            , a.description
        from @T a
  ),

  cte2 as (
  select    a.ID
            , n.r.value('.', 'varchar(50)') "Value"
    , row_number() OVER(PARTITION BY a.ID ORDER BY a.ID) rn
            , a.description
              from cte a
  cross apply (select cast('<r>'+replace(replace(Col,'&','&'), ' ', '</r><r>')+'</r>' as xml)) as S(XMLCol)
  cross apply S.XMLCol.nodes('r') as n(r)
  
  )

  select *
  from cte2 a
  pivot 
 (max(value) for a.rn in ([1], [2], [3], [4])) as "Pivot"

fiddle