CSV to columns, join with row-based data, analyze and output - can it be done efficiently?, Sql Server

Language: Layout:

CREATE TABLE students
    ([id] int, [oldSkill] varchar(21), [newSkill] varchar(36))
;
    
INSERT INTO students
    ([id], [oldSkill], [newSkill])
VALUES
    (1, 'Word', 'Excel,PowerPoint,Word'),
    (2, 'Excel,PowerPoint,Word', 'Excel,Outlook,PowerPoint,Word'),
    (3, 'PowerPoint,Word', 'Excel,PowerPoint,Word'),
    (4, 'Access,Excel', 'Access,Excel,Outlook,PowerPoint,Word'),
    (5, 'Outlook,Word', 'Excel,Outlook,PowerPoint,Word')
;

CREATE TABLE skills
    ([id] int, [skill] varchar(10), [assignment] varchar(1))
;
    
INSERT INTO skills
    ([id], [skill], [assignment])
VALUES
    (1, 'Word', 'B'),
    (1, 'Word', 'P'),
    (2, 'Excel', 'P'),
    (2, 'PowerPoint', 'B'),
    (2, 'PowerPoint', 'P'),
    (2, 'Word', 'P'),
    (3, 'PowerPoint', 'P'),
    (3, 'Word', 'P'),
    (4, 'Access', 'B'),
    (4, 'Excel', 'B'),
    (4, 'Access', 'P'),
    (4, 'Excel', 'P'),
    (5, 'Outlook', 'P'),
    (5, 'Word', 'B')
;

CREATE TABLE desiredOut
    ([id] int, [skill_1] varchar(6), [skill_1_primary] varchar(1), [skill_1_backup] varchar(4), [skill_2] varchar(10), [skill_2_primary] varchar(1), [skill_2_backup] varchar(4), [skill_3] varchar(10), [skill_3_primary] varchar(1), [skill_3_backup] varchar(4), [skill_4] varchar(10), [skill_4_primary] varchar(4), [skill_4_backup] varchar(4), [skill_5] varchar(4), [skill_5_primary] varchar(1), [skill_5_backup] varchar(4))
;
    
INSERT INTO desiredOut
    ([id], [skill_1], [skill_1_primary], [skill_1_backup], [skill_2], [skill_2_primary], [skill_2_backup], [skill_3], [skill_3_primary], [skill_3_backup], [skill_4], [skill_4_primary], [skill_4_backup], [skill_5], [skill_5_primary], [skill_5_backup])
VALUES
    (1, 'Excel', 'Y', NULL, 'PowerPoint', 'Y', NULL, 'Word', 'Y', 'Y', NULL, NULL, NULL, NULL, NULL, NULL),
    (2, 'Excel', 'Y', NULL, 'Outlook', 'Y', NULL, 'PowerPoint', 'Y', 'Y', 'Word', 'Y', NULL, NULL, NULL, NULL),
    (3, 'Excel', 'Y', NULL, 'PowerPoint', 'Y', NULL, 'Word', 'Y', NULL, NULL, NULL, NULL, NULL, NULL, NULL),
    (4, 'Access', 'Y', 'Y', 'Excel', 'Y', 'Y', 'Outlook', 'Y', NULL, 'PowerPoint', 'Y', NULL, 'Word', 'Y', NULL),
    (5, 'Excel', 'Y', NULL, 'Outlook', 'Y', NULL, 'PowerPoint', 'Y', NULL, 'Word', NULL, 'Y', NULL, NULL, NULL)
;

-- this pivots the skills table into a single row for each skill
select *
into #skillPiv
from 
(
  select id, skill, assignment,
    'assignment_'+cast(row_number() over(partition by id, skill order by skill) as varchar(10)) rn
  from skills
) d
pivot
(
  max(assignment)
  for rn in ([assignment_1], [assignment_2])
) piv
order by id;

-- this converts the student's oldSkills from CSV into rows and looks up the corresponding skill assignments in the #skills table
with st(id, skill, oldSkill) as (
select id, LEFT(CAST(oldSkill as varchar(max)), CHARINDEX(',',oldSkill+',')-1),
    STUFF(CAST(oldSkill as varchar(max)), 1, CHARINDEX(',',oldSkill+','), '')
from students
union all
select id, LEFT(CAST(oldSkill as varchar(max)), CHARINDEX(',',oldSkill+',')-1),
    STUFF(CAST(oldSkill as varchar(max)), 1, CHARINDEX(',',oldSkill+','), '')
from st
where oldSkill > ''
)
select st.id
    ,st.skill
    ,CASE WHEN sp.assignment_1 = 'P' OR sp.assignment_2 = 'P'
        THEN 'Y'
        ELSE ''
        END AS [primary]
    ,CASE WHEN sp.assignment_1 = 'B' OR sp.assignment_2 = 'B'
        THEN 'Y'
        ELSE ''
        END AS [backup]
into #oldSkills
from st
inner join #skillPiv sp on st.id = sp.id and st.skill = sp.skill
order by id;

-- convert the newSkills column from CSV to rows and insert our default skill assignment values
with tmp(id, skill, newSkill) as (
select id, LEFT(CAST(newSkill as varchar(max)), CHARINDEX(',',newSkill+',')-1),
    STUFF(CAST(newSkill as varchar(max)), 1, CHARINDEX(',',newSkill+','), '')
from students
union all
select id, LEFT(CAST(newSkill as varchar(max)), CHARINDEX(',',newSkill+',')-1),
    STUFF(CAST(newSkill as varchar(max)), 1, CHARINDEX(',',newSkill+','), '')
from tmp
where newSkill > ''
)
select id
    ,skill
    ,'Y' as [primary]
    ,'' as [backup]
into #newSkills
from tmp
where skill NOT IN (
    select skill from #oldSkills where id = tmp.id
    )
order by id;

-- now combine #oldSkills and #newSkills into one table that has all the values we need
select *
into #studentSkills
from (
    select * from #newSkills
    UNION
    select * from #oldSkills
) as ss;

select * from #studentSkills;

View schema
Execution time: 0,08 sec, rows selected: 19, rows affected: 24, absolute service time: 0,22 sec

CSV to columns, join with row-based data, analyze and output - can it ...