Display Pivoted data in SQL Server

Hi makumbi,

Plese refer below sample query.

SQL

CREATE TABLE #student
(
	[Name]  VARCHAR(50),
	[Class] VARCHAR(20),
	[Str]   VARCHAR(10),
	[ENG]   VARCHAR(10),
	[Set]   VARCHAR(20)
)
INSERT INTO #student VALUES('IYAMULEMYE DENZEL OSBERT','P7','L','83','SET1');
INSERT INTO #student VALUES('NANYONJO LAURYN ROSETTE','P7','L','83','SET1');
INSERT INTO #student VALUES('IYAMULEMYE DENZEL OSBERT','P7','L','89','SET2');
INSERT INTO #student VALUES('NANYONJO LAURYN ROSETTE','P7','L','70','SET2');
INSERT INTO #student VALUES('IYAMULEMYE DENZEL OSBERT','P7','L','50','SET3');
INSERT INTO #student VALUES('NANYONJO LAURYN ROSETTE','P7','L','60','SET3');


DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName =ISNULL(@ColumnName +',','')+ QUOTENAME([Set]) FROM (SELECT DISTINCT [Set] FROM #student) AS S

SET @DynamicPivotQuery ='
;WITH CTE AS (SELECT DISTINCT Name,[Set],Eng FROM #student)
SELECT Name, '+@ColumnName+', ''ENG'' Subject FROM CTE
PIVOT (MAX(ENG) FOR [Set] IN('+@ColumnName+')) p'
EXEC(@DynamicPivotQuery)	

DROP TABLE #student

Output