Splitting (duplicating rows of) an SQL table based on the values in one column

Question

I have a table which is the result of an export and has the columns and values like the following

Name   Aliases          Ranks
Ben    BenA BenB BenC   1 5 3  
Jerry  JerryA JerryB    7 3 

Aliases and Ranks are separated by a character (in this case CHAR(10)) and they have the same number of entries. But each Name could have different number of Aliases (And therefore Ranks).

I would like to write a SQL Query to give me the following table

Name   Alias     Rank
Ben    BenA      1      
Ben    BenB      5 
Ben    BenC      3
Jerry  JerryA    7
Jerry  JerryB    3 

How can I do this?

Answer 1

with cte as (
    select Name, cast(null as int) as AliasStartPosition, cast(0 as int) as AliasEndPosition, Aliases + ' ' as Aliases, cast(null as int) as RankStartPosition, cast(0 as int) as RankEndPosition, Ranks + ' ' as Ranks
    from (
        values ('Ben', 'BenA BenB BenC', '1 5 3'),
               ('Jerry', 'JerryA JerryB', '7 3')
    ) t (Name, Aliases, Ranks)

    union all

    select Name, AliasEndPosition + 1, charindex(' ', Aliases, AliasEndPosition + 1), Aliases, RankEndPosition + 1, charindex(' ', Ranks, RankEndPosition + 1), Ranks
    from cte
    where charindex(' ', Aliases, AliasEndPosition + 1) != 0
)
select Name, substring(Aliases, AliasStartPosition, AliasEndPosition - AliasStartPosition) as Alias, substring(Ranks, RankStartPosition, RankEndPosition - RankStartPosition) as Rank 
from cte
where AliasStartPosition is not null