Untitled

WITH CandidateSkills AS (
    SELECT 
        c.candidatename,
        s.skill,
        s.experience,
        ROW_NUMBER() OVER(PARTITION BY s.skill ORDER BY s.experience DESC) AS rank
    FROM 
        candidates c
    CROSS APPLY STRING_SPLIT(c.skills, ',') AS s
    CROSS APPLY STRING_SPLIT(c.experience_years, ',') AS e
    WHERE CHARINDEX(s.value, c.skills) = CHARINDEX(e.value, c.experience_years)
)
SELECT 
    candidatename,
    skill,
    experience AS highest_experience
FROM 
    CandidateSkills
WHERE 
    rank = 1;