Error in Pivot - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Error in Pivot

Author

Topic

sital
Yak Posting Veteran

89 Posts

Posted - 2009-04-20 : 09:15:49

Hi

Can any one identify any error in the following query?

select StudentID,[100] as '1',[50] as '2' from
(select StudentID,Total,Average from mySchema.compute1) p pivot
(count(StudentID) for mark1 in ([100],[50])) as Pvt order by mark1

For your reference I m including the Schema creation and table creation and also insert statements:

create schema mySchema

create table mySchema.compute1(StudentID int,Mark1 int,mark2 int,mark3 int,mark4 int,mark5 int,Total int,Average int)

insert mySchema.compute1(StudentID,mark1,mark2,mark3,mark4,mark5) values(1,100,100,100,100,100)

insert mySchema.compute1(StudentID,mark1,mark2,mark3,mark4,mark5) values(2,90,80,70,60,50)

insert mySchema.compute1(StudentID,mark1,mark2,mark3,mark4,mark5) values(3,50,60,70,80,90)

select * from mySchema.compute1

update mySchema.compute1 set Total = mark1+mark2+mark3+mark4+mark5

update myschema.compute1 set Average = total/5

How ever when I execute the Select Query with the Pivot it is giving me the following Error:

select StudentID,[100] as '1',[50] as '2' from
(select StudentID,Total,Average from mySchema.compute1) p pivot
(count(StudentID) for mark1 in ([100],[50])) as Pvt order by mark1

Please help me in identifying the error.

Thanks in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-20 : 09:24:03

1. update myschema.compute1 set Average = total / 5.0E -- to avoid integer division
2. Are you using SQL Server 2005 AND have your compatibility level set to 90 or higher?

E 12°55'05.63"
N 56°04'39.26"

sital
Yak Posting Veteran

89 Posts

Posted - 2009-04-21 : 12:25:51

quote:
Originally posted by Peso

1. update myschema.compute1 set Average = total / 5.0E -- to avoid integer division
2. Are you using SQL Server 2005 AND have your compatibility level set to 90 or higher?

E 12°55'05.63"
N 56°04'39.26"

Thanks for your reply.

Please note that I m using SQL server 2008.

The error is shown when I execute the select statement with Pivot clause, that is when I execute the following statement:

select StudentID,[100] as '1',[50] as '2' from
(select StudentID,Total,Average from mySchema.compute1) p pivot
(count(StudentID) for mark1 in ([100],[50])) as Pvt order by mark1

Can you identify any error?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-21 : 13:28:20

Which error message do you receive?

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources