Untitled

SELECT 
    c.candidatename,
    t.skill,
    t.experience AS highest_experience
FROM 
    candidates c
JOIN (
    SELECT 
        SUBSTRING_INDEX(skills, ',', 1) AS skill,
        CAST(SUBSTRING_INDEX(experience_years, ',', 1) AS UNSIGNED) AS experience
    FROM 
        candidates
    UNION ALL
    SELECT 
        SUBSTRING_INDEX(SUBSTRING_INDEX(skills, ',', numbers.n), ',', -1) AS skill,
        CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(experience_years, ',', numbers.n), ',', -1) AS UNSIGNED) AS experience
    FROM 
        candidates
    JOIN (
        SELECT 
            (a.N + b.N * 10 + 1) AS n
        FROM 
            (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
        CROSS JOIN 
            (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
    ) AS numbers ON CHAR_LENGTH(skills) - CHAR_LENGTH(REPLACE(skills, ',', '')) >= numbers.n - 1
) AS t ON SUBSTRING_INDEX(c.skills, ',', 1) = t.skill
        AND CAST(SUBSTRING_INDEX(c.experience_years, ',', 1) AS UNSIGNED) = t.experience
GROUP BY 
    c.candidatename, t.skill;