SQL Server 2022

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

/* Dummy Table Creation */

DECLARE @DummyWeightageTable TABLE (
    Category varchar(50)
    , Fieldname varchar(50)
    , Weightage real
);

INSERT INTO @DummyWeightageTable
VALUES
('Sniper', 'Eyesight', 40), 
('Sniper', 'BMI', 10), 
('Sniper', 'Height', 10), 
('Sniper', 'Skill', 20), 
('Fighter', 'Eyesight', 10), 
('Fighter', 'BMI', 30), 
('Fighter', 'Height', 20);

/* Actual Functionality */

DECLARE @Category TABLE (
    Category varchar(50)
    , Done bit NOT NULL DEFAULT(0)
);

-- Capture all categories so we can loop through them
INSERT INTO @Category (Category)
SELECT Category
FROM @DummyWeightageTable
GROUP BY Category;

-- Dynamic SQL must use nvarchar
DECLARE @sql NVARCHAR(MAX), @delta NVARCHAR(MAX), @CurrentCategory varchar(50);

-- Loop through all categories
WHILE EXISTS (SELECT 1 FROM @Category WHERE Done = 0) BEGIN
    SELECT TOP 1 @CurrentCategory = Category FROM @Category WHERE Done = 0;

-- Display the category at least for debugging
    SET @sql = 'SELECT Emp_Id, ' + @CurrentCategory + ', ';

-- First concat the values required from each row
    -- Then string_agg then into a single string
    SELECT @delta = STRING_AGG(CONCAT('(', Weightage, ' * ISNULL(', Fieldname, ',0))'), ' + ')
    FROM @DummyWeightageTable
    WHERE Category = @CurrentCategory;

SET @sql = @sql + @delta + ' from MyDataTable1;';

-- This is how you debug dynamic SQL
    PRINT @sql;
    --EXEC sp_executesql @sql;

UPDATE @Category SET Done = 1 WHERE Category = @CurrentCategory;
END;

SELECT Emp_Id, Fighter, (10 * ISNULL(Eyesight,0)) + (30 * ISNULL(BMI,0)) + (20 * ISNULL(Height,0)) from MyDataTable1;
SELECT Emp_Id, Sniper, (40 * ISNULL(Eyesight,0)) + (10 * ISNULL(BMI,0)) + (10 * ISNULL(Height,0)) + (20 * ISNULL(Skill,0)) from MyDataTable1;
11 rows affected