Convert column to multiple rows without Split_Lines - SQL Server 2008

Question

I would like to convert the following SQL table:

User_Index   Emails
------------------------
  5          [email protected];[email protected]
  10         [email protected];[email protected]

into:

  User_Index   Emails
------------------------
  5          [email protected]
  5          [email protected]
  10         [email protected]
  10         [email protected]

I am using SQL Server 2008 so SPLIT_LINES function does not work, I am trying to split the emails column by the semi-colon value.

Answer 1

One method is a recursive CTE:

with cte as (
      select user_index, convert(varchar(max), null) as email, convert(varchar(max), emails + ';') as rest
      from t
      union all
      select user_index, left(rest, charindex(';', rest) - 1), stuff(rest, 1, charindex(';', rest) + 1, '')
      from cte
      where rest <> ''
     )
select user_index, email
from cte
where email is not null;

Here is a db<>fiddle.