Database

KipkoechE wrote:

i wish to know how i can loop through the records of each student and insert into TotalScore column

UPDATE tablename SET TotalScore = (unit1 + unit2 + unit3 + unit4) // and so on?

KipkoechE wrote:

the number of columns can be changed at runtime

DECLARE @temp NVARCHAR(MAX)
SELECT @temp = COALESCE(@temp + ', ', '') + field1 
FROM table

SELECT
    STUFF(
        (SELECT '/' + Surname + ',' + STUFF(
            (SELECT '-' + Forename 
            FROM YourTable As T2 
            WHERE T2.Surname = T1.Surname 
            ORDER BY T2.Forename 
            FOR XML PATH(''), TYPE
        ).value('.', 'varchar(max)')
        , 1, 1, '')
        FROM YourTable As T1
        GROUP BY Surname
        ORDER BY Surname
        FOR XML PATH(''), TYPE
    ).value('.', 'varchar(max)')
    , 1, 1, '') As CombinedNames
;

SELECT
    STUFF(
        (SELECT '/' + Surname + ',' + STUFF(
            (SELECT '-' + Forename 
            FROM YourTable As T2 
            WHERE T2.Surname = T1.Surname 
            ORDER BY T2.Forename 
            FOR XML PATH(''), TYPE
        ).value('.', 'varchar(max)')
        , 1, 1, '')
        FROM YourTable As T1
        
        -- Filter here:
        WHERE T1.ID = @TheIDToFind
        
        GROUP BY Surname
        ORDER BY Surname
        FOR XML PATH(''), TYPE
    ).value('.', 'varchar(max)')
    , 1, 1, '') As CombinedNames
;

DECLARE @T TABLE
(
	ID int NOT NULL,
	Surname varchar(10) NOT NULL,
	Forename varchar(10) NOT NULL
);

INSERT INTO @T (ID, Surname, Forename)
VALUES
	(7, 'Vaugh', 'William'),
	(7, 'Vaugh', 'Smith'),
	(6, 'Woods', 'Jane'),
	(6, 'Woods', 'Joseph'),
	(6, 'Wright', 'Adam'),
	(6, 'Wright', 'John')
;

SELECT
    STUFF(
        (SELECT '/' + Surname + ',' + STUFF(
            (SELECT '-' + Forename 
            FROM @T As T2 
            WHERE T2.Surname = T1.Surname 
            ORDER BY T2.Forename 
            FOR XML PATH(''), TYPE
        ).value('.', 'varchar(max)')
        , 1, 1, '')
        FROM @T As T1
		WHERE T1.ID = 6
        GROUP BY Surname
        ORDER BY Surname
        FOR XML PATH(''), TYPE
    ).value('.', 'varchar(max)')
    , 1, 1, '') As CombinedNames
;

-- Output:
-- Woods,Jane-Joseph/Wright,Adam-John