Common Table Expression in SQL Server 2000 Error: Incorrect syntax near the keyword WITH

Hi makumbi,

Common Table Expression (CTE) is not available in SQL Server 2000.

So you need to use Temporary table.

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

Output