Display Pivoted data in SQL Server

makumbi
 
on Sep 29, 2022 01:01 AM
425 Views

How can i display this using SQL Server.

Name Class: Str ENG  
IYAMULEMYE DENZEL OSBERT P7 L 83 SET1
NANYONJO LAURYN ROSETTE P7 L 83 SET1
         
Name Class: Str ENG  
IYAMULEMYE DENZEL OSBERT P7 L 89 SET2
NANYONJO LAURYN ROSETTE P7 L 70 SET2
         
Name Class: Str ENG  
IYAMULEMYE DENZEL OSBERT P7 L 50 SET3
NANYONJO LAURYN ROSETTE P7 L 60 SET3

This is how i want it to be displayed

Name SET1 SET2 SET3 Subject
IYAMULEMYE DENZEL OSBERT 83 89 50 ENG
NANYONJO LAURYN ROSETTE 83 70 60 ENG
Download FREE API for Word, Excel and PDF in ASP.Net: Download
PrinceG
 
on Sep 29, 2022 04:55 AM

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

Name SET1 SET2 SET3 Subject
IYAMULEMYE DENZEL OSBERT 83 89 50 ENG
NANYONJO LAURYN ROSETTE 83 70 60 ENG