How to split a column into multiple columns by condition delimeter in SQL?

could anyone help me please?

I have this column

Column<br/>

John > Doe Doe > Writer<br/>

Jane > Doe > Chemical Engineer > Sovena<br/>

I want to my final result to be

Column<br/>

John<br/>

Jane<br/>

Column2<br/>

Doe Doe<br/>

Doe<br/>

Column3<br/>

Writer<br/>

Chemical Engineer<br/>

Column4<br/>

(Empty) <br/>

Sovena<br/>

I need this in SQL, split by “>” could you help? Thanks!

Answer

This answers the original version of the question.

Unfortunately, the string splitting functionality that is built-in does not provide an index for the components. So, one solution is a recursive subquery:

with cte as (
      select id, convert(varchar(max), NULL) as str,
             convert(varchar(max), str) as rest, 0 as lev
      from t
      union all
      select id, left(rest, charindex(' > ', rest + ' > ') - 1),
             stuff(rest, 1, charindex(' > ', rest + ' > ') + 2, ''),
             lev + 1
      from cte
      where rest <> '' 
     )
select max(case when lev = 1 then str end),
       max(case when lev = 2 then str end),
       max(case when lev = 3 then str end),
       max(case when lev = 4 then str end)
from cte
group by id;

Here is a db<>fiddle.