Hi makumbi,
Please refer below sample Query.
SQL
CREATE TABLE #Student
(
[NAME] VARCHAR(50),
[SCORE] VARCHAR(20),
[Subject] VARCHAR(20),
[SET] VARCHAR(20)
)
INSERT INTO #Student VALUES ('OBUYA EMMANUEL OKIRIA',149,'SST','SET1')
INSERT INTO #Student VALUES ('MUGUME JESSE',547,'ENG','SET1')
INSERT INTO #Student VALUES ('MWEBAZE PAUL',734,'MATHS','SET1')
INSERT INTO #Student VALUES ('OCHWO OWOR A GABRIEL',714,'ENG','SET1')
INSERT INTO #Student VALUES ('KANSIIME HOPE MWESIGYE',734,'ENG','SET2')
INSERT INTO #Student VALUES ('KARUNGI DIANA EVELYN',714,'SST','SET3')
INSERT INTO #Student VALUES ('NALUYOMBYA MARIAM',665,'SST','SET4')
INSERT INTO #Student VALUES ('NAMUKOSE NANCY S NIOLA',812,'ENG','SET5')
INSERT INTO #Student VALUES ('LUBEGA AQRAM KADDUNABBI',557,'ENG','SET6')
INSERT INTO #Student VALUES ('LUWALAGA DAVID JOEL',458,'SST','SET7')
INSERT INTO #Student VALUES ('NINSIIMA PHIONA',646,'MATHS','SET8')
INSERT INTO #Student VALUES ('AMAHORO ABIGAIL MAHIGIGI',812,'ENG','SET9')
INSERT INTO #Student VALUES ('BANYA EMILY',714,'ENG','SET10')
INSERT INTO #Student VALUES ('BUKIRWA PRISCILLA SSETAMU',802,'MATHS','SET11')
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName = ISNULL(@ColumnName +',','')+ QUOTENAME([SET]) FROM
(
SELECT TOP 1000 [SET] FROM (SELECT DISTINCT [SET] FROM #student) y
ORDER BY SUBSTRING([SET], 0,PATINDEX('%[0-9]%',[SET]))+RIGHT ('00000' + SUBSTRING([SET], PATINDEX('%[0-9]%',[SET]) , LEN([SET])),5)
) AS S
SET @DynamicPivotQuery ='
SELECT DISTINCT NAME,[SET],SCORE,[Subject]
INTO #Test
FROM #student
SELECT NAME, '+@ColumnName+', Subject FROM #Test
PIVOT (MAX(SCORE) FOR [SET] IN('+@ColumnName+')) p'
EXEC(@DynamicPivotQuery)
DROP TABLE #Student
Screenshot
