SQL Server Error: Incorrect syntax near the keyword as in dynamic Pivot query

Last Reply one year ago By dharmendr

Posted one year ago

 

declare @StdID varchar(50)
Set @StdID= 'R-000003'
select * into #TempMarks 
from ( select * 
		from ( select [SubjectID],[SetExamID],[Marks] 
		from tblSetMarks where AdmissionNo= @StdID as tbl
		pivot (sum(Marks) for [SetExamID] in ([14],[15])) as PVT
) as s
select *,[14]+[15] as 'Grand Total' from #TempMarks
union all
select 'Total', sum([14]), sum ([15]), sum ([14]+[15]) from #TempMarks
drop table #TempMarks

This query is showing error Incorrect syntax near the keyword 'as'. and my table is 

SetExamID

ClassID

SectionID

SubjectID

AdmissionNo

Marks

Total Marks

14

1

50

10

R-0001

20

25

14

1

50

11

R-0002

15

25

14

1

50

12

R-0003

21

25

15

1

50

10

R-0001

35

50

15

1

50

11

R-0002

24

50

15

1

50

12

R-0003

29

50

Posted one year ago

Hi smile,

Check the below modified query.

SQL

declare @StdID varchar(50)
Set @StdID= 'R-0003'
select * into #TempMarks
from ( 
select *
from ( 
	select [SubjectID],[SetExamID],[Marks]
	from tblSetMarks
	where AdmissionNo= @StdID
	)
	as tbl 
pivot (sum(Marks) for [SetExamID] in ([14],[15])
) as PVT
) as s
select CONVERT(VARCHAR(20),SubjectID) SubjectID,[14],[15],[14]+[15] as 'Grand Total' from #TempMarks
union all
select 'Total', sum([14]), sum ([15]), sum ([14]+[15]) from #TempMarks
drop table #TempMarks

Output

SubjectID 14 15 Grand Total
12 21 29 50
Total 21 29 50

Posted one year ago

Hi smile,

Such unexpected problems can appear when you copy the code from a web page or email and the text contains unprintable characters.

So write the query by your self instead of copying it.