SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

declare @categoryID int = 163,
@Ids nvarchar(2000) = '1,2,3',
@Names nvarchar(2000) = 'Bob,Joe,Alex';

with cte as (
      select @categoryId as categoryId,
             convert(varchar(max), left(@ids, charindex(',', @ids + ',') - 1)) as id,
             convert(varchar(max), left(@names, charindex(',', @names + ',') - 1)) as name,
             convert(varchar(max), stuff(@ids, 1, charindex(',', @ids + ','), '')) as rest_ids,
             convert(varchar(max), stuff(@names, 1, charindex(',', @names + ','), '')) as rest_names, 1 as lev
      union all
      select categoryId,
             convert(varchar(max), left(rest_ids, charindex(',', rest_ids + ',') - 1)) as id,
             convert(varchar(max), left(rest_names, charindex(',', rest_names + ',') - 1)) as name,
             convert(varchar(max), stuff(rest_ids, 1, charindex(',', rest_ids + ','), '')) as rest_ids,
             convert(varchar(max), stuff(rest_names, 1, charindex(',', rest_names + ','), '')) as rest_names, lev + 1
      from cte
      where lev < 10 and rest_ids <> ''
     )
select categoryid, id, name
from cte

categoryid	id	name
163	1	Bob
163	2	Joe
163	3	Alex