Order VARCHAR Columns in dynamic Pivot query in SQL Server

makumbi
 
on Sep 29, 2022 08:52 AM
411 Views

How can i display this by the set order example set1, set2, set3 etc in that corresponding order.

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
  
SELECT @ColumnName =ISNULL(@ColumnName +',','')+ QUOTENAME([Type]) FROM (SELECT DISTINCT [Type] FROM extractdataarchive)  as s
  
SET @DynamicPivotQuery ='
WITH CTE AS (SELECT DISTINCT Name,[type],Engscore FROM extractdataarchive)
SELECT Name, '+@ColumnName+', ''ENG'' Subject FROM CTE
PIVOT (MAX(ENGScore) FOR [type] IN('+@ColumnName+')) p'
EXEC(@DynamicPivotQuery)
NAME Engscore engrade Subject Type
OBUYA EMMANUEL OKIRIA 14 9 ENG SET1
MUGUME JESSE 54 7 ENG SET1
MWEBAZE  PAUL 73 4 ENG SET1
OCHWO OWOR A GABRIEL 71 4 ENG SET1
OMODING MOSES MARVIN 58 7 ENG SET1
ORUK OPYENE E ELAINE 90 1 ENG SET1
OUKO NICOLE AWUOR 77 3 ENG SET1
PALATHINGAL JAISON GEORGE 81 2 ENG SET1
WANTANTE ANGEL 64 6 ENG SET1
KAMURE ALATARA  DANIEL F 47 8 ENG SET1
KANSIIME HOPE MWESIGYE 73 4 ENG SET2
KARUNGI DIANA EVELYN 71 4 ENG SET3
NALUYOMBYA MARIAM 66 5 ENG SET4
NAMUKOSE NANCY S NIOLA 81 2 ENG SET5
LUBEGA AQRAM KADDUNABBI 55 7 ENG SET6
LUWALAGA DAVID JOEL 45 8 ENG SET7
NINSIIMA PHIONA 64 6 ENG SET8
AMAHORO ABIGAIL MAHIGIGI 81 2 ENG SET9
BANYA EMILY 71 4 ENG SET10
BUKIRWA PRISCILLA SSETAMU 80 2 ENG SET11

sets are coming out well but not in order how can i order them like set1,set2,set3 etc. in that order

Name Set13 Set5 Set16 Set14 Set9 SET1 Set10 Set6 Set3 Set15 Set12 set2 Set11 Set8 Set7 Set4 Subject
SEMBUYA MARCUS KAAYA. NULL 82 NULL NULL 83 85 94 99 91 NULL NULL NULL NULL 99 78 NULL ENG
AANIKA NATHAN NULL NULL NULL NULL NULL NULL 69 NULL NULL NULL NULL 73 NULL NULL NULL 68 ENG
AASMINE SANGAM NULL 86 NULL NULL NULL 78 NULL 86 80 NULL NULL 89 NULL 92 86 89 ENG
Download FREE API for Word, Excel and PDF in ASP.Net: Download
PrinceG
 
on Sep 30, 2022 01:48 AM
on Sep 30, 2022 02:02 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,'ENG','SET1')
INSERT INTO #Student VALUES ('MUGUME JESSE',547,'ENG','SET1')
INSERT INTO #Student VALUES ('MWEBAZE  PAUL',734,'ENG','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,'ENG','SET3')
INSERT INTO #Student VALUES ('NALUYOMBYA MARIAM',665,'ENG','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,'ENG','SET7')
INSERT INTO #Student VALUES ('NINSIIMA PHIONA',646,'ENG','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,'ENG','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