By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
7 rows affected
SELECT
Emp_Id,
'Fighter' AS Category,
(10 * ISNULL(Eyesight,0)) + (30 * ISNULL(BMI,0)) + (20 * ISNULL(Height,0))
FROM MyDataTable1 dt
UNION ALL
SELECT
Emp_Id,
'Sniper' AS Category,
(40 * ISNULL(Eyesight,0)) + (10 * ISNULL(BMI,0)) + (10 * ISNULL(Height,0)) + (20 * ISNULL(Skill,0))
FROM MyDataTable1 dt