Pivoting data works in one db but not another

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

SQL Server 2005 Forums

Transact-SQL (2005)

Pivoting data works in one db but not another

Author

Topic

seanw122
Starting Member

2 Posts

Posted - 2008-03-20 : 17:04:59

In the following code examples I got to learn PIVOT, I found an error for SUM. However when this is ran against the AdventureWorks db it works fine. Notice it is using a table variable and not an actual table. What do I need to do to my db to get this to work?

Thanks!

declare @sales table
(
[Year] int,
Quarter char(2),
Amount float
)

insert into @sales values(2001, 'Q1', 70)
insert into @sales values(2001, 'Q1', 150)
insert into @sales values(2002, 'Q1', 20)
insert into @sales values(2001, 'Q2', 15)
insert into @sales values(2002, 'Q2', 25)
insert into @sales values(2001, 'Q3', 50)
insert into @sales values(2002, 'Q3', 20)
insert into @sales values(2001, 'Q4', 90)
insert into @sales values(2001, 'Q4', 80)
insert into @sales values(2002, 'Q4', 35)

select * from @sales
PIVOT (
SUM(Amount)
for Quarter in (Q1, Q2, Q3, Q4)) as p

Yields...
Incorrect syntax near 'SUM'.

seanw122
Starting Member

2 Posts

Posted - 2008-03-20 : 17:19:36

I just found the answer!

In another sqlteam forum I found someone referring to sp_help on a table. I used sp_helpdb on both my db and the AdventureWorks db. I finally noticed a difference. The compatibility level on my db was at 80 and the Ad-Works was at 90. I found http://blog.sqlauthority.com/2007/05/29/sql-server-2005-change-database-compatible-level-backward-compatibility/ and it shows the compatibility levels of the different version of Sql Server.

I ran EXEC sp_dbcmptlevel MyDB, 90; to change the db to a compability level for 2005. My code now works. I didn't know that the db was stuck at a SQL Server 2000 level.

..:: Sean ::..

http://videos.tulsatechfest.com http://tulsatechfest.com Coming Oct 2008!

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2008-03-23 : 22:55:31

Cool... thanks for the feedback.

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"
For better, quicker answers, click on the following... [url]http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources