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

makumbi
 
on Sep 30, 2022 01:50 AM
437 Views

show error below how can i modify it to run on sqlserver 2000

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WITH'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '('.

 

DECLARE @DynamicPivotQuery AS NVARCHAR(550)
DECLARE @ColumnName AS NVARCHAR(500)
 
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)

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Sep 30, 2022 02:00 AM

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

NAME SET1 SET2 SET3 SET4 SET5 SET6 SET7 SET8 SET9 SET10 SET11 Subject
AMAHORO ABIGAIL MAHIGIGI                 812     ENG
BANYA EMILY                   714   ENG
KANSIIME HOPE MWESIGYE   734                   ENG
LUBEGA AQRAM KADDUNABBI           557           ENG
MUGUME JESSE 547                     ENG
NAMUKOSE NANCY S NIOLA         812             ENG
OCHWO OWOR A GABRIEL 714                     ENG
BUKIRWA PRISCILLA SSETAMU                     802 MATHS
MWEBAZE  PAUL 734                     MATHS
NINSIIMA PHIONA               646       MATHS
KARUNGI DIANA EVELYN     714                 SST
LUWALAGA DAVID JOEL             458         SST
NALUYOMBYA MARIAM       665               SST
OBUYA EMMANUEL OKIRIA 149                     SST