Select (Add) extra column in Pivot query in SQL Server

makumbi
 
on Sep 29, 2022 11:35 PM
457 Views
NAME SCORE GRADE Subject SET
LAMARO MARTHA 85 2 ENG Set1
MULINDWA DAVID 93 2 MATHS Set1
MUTASA NSOWE SAMI 68 4 MATHS Set1
NABAGEREKA SYLIVIA 80 2 MATHS Set1
NAKALEMA KEVIN ELIZABETH 93 2 SST Set2
OLOBO KATHERINE KEISHA 91 2 SST Set2
SSESANGA TYLER JAYDON 71 3 SST Set2
KAWUMA ALICIA HALIMA MUYINDA 73 3 ENG Set3
NAKIIRYA DANIELLA 92 2 MATHS Set3
NAMUGENYI RAHMA 76 3 MATHS Set4
SSEKANJAKO HASHIN 92 2 SST Set4
LAGARA MICHAEL HOSEA 88 2 SST Set4

 

PROCEDURE [dbo].[MarksheetSubjectdata] AS
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
  
SELECT @ColumnName =ISNULL(@ColumnName +',','')+ QUOTENAME([Type]) FROM (SELECT DISTINCT [Type] FROM extractdataarchiveunion) as s
  
SET @DynamicPivotQuery ='
WITH CTE AS (SELECT DISTINCT Names,[type],score FROM extractdataarchiveunion)
SELECT Names, '+@ColumnName+', ''ENG'' Subject FROM CTE
PIVOT (MAX(Score) FOR [type] IN('+@ColumnName+')) p'
EXEC(@DynamicPivotQuery)

This code displays all others subjects as Eng

how can i modify it to display other subjects in the list

please help

Download FREE API for Word, Excel and PDF in ASP.Net: Download
PrinceG
 
on Sep 30, 2022 01:51 AM
on Sep 30, 2022 01:58 AM

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