SQL 2005 Pivot Table Problem - 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

SQL Server 2005 Forums

Transact-SQL (2005)

SQL 2005 Pivot Table Problem

Author

Topic

dprichard
Yak Posting Veteran

94 Posts

Posted - 2008-04-16 : 09:59:33

I have SQL 2005 and am trying to do a pivot table. I am running into a lot of challenges. The first thing I am running into is it giving me the following error:

The following errors were encountered while parsing the contents of the SQL Pane: The PIVOT SQL construct or statement is not supported.

My table has the following columns:

Client
Dollars Billed
SlipDates

The there is a slip date for each client for for april 1, then may 1, etc.

I am trying to get it to list out like this

____________April2007_________May2007
Client1_____DollarsBilled_____DollarsBilled
Client2_____DollarsBilled_____DollarsBilled
Client3_____DollarsBilled_____DollarsBilled

Here is my current attempt.

SELECT     Client, [4/1/2007 12:00:00 AM] AS April2007, [5/1/2007 12:00:00 AM] AS May2007
FROM         (SELECT     Client, DollarsBilled, SlipDates
                       FROM          dbo.MonthlyClientBillables) p PIVOT (SUM(DollarsBilled) FOR Client IN ([4/1/2007 12:00:00 AM], [5/1/2007 12:00:00 AM])) AS pvt
ORDER BY Client

Any help would be greatly appreciated.

raky
Aged Yak Warrior

767 Posts

Posted - 2008-04-16 : 10:12:01

the compatibility level of the database must be set to 90.check it once.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-16 : 10:13:27

Check the compatibility level of your db using db_cmptlevel and make sure its 90 for using PIVOT.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-16 : 10:15:07

After that, you'll probably need to change "FOR Client" to "FOR SlipDates"...

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

bfoster
Starting Member

30 Posts

Posted - 2008-04-16 : 10:21:04

I had to change one thing in your query to make it work on my computer. See the red text.

SELECT Client, [4/1/2007 12:00:00 AM] AS April2007, [5/1/2007 12:00:00 AM] AS May2007
FROM (SELECT Client, DollarsBilled, SlipDates
FROM dbo.MonthlyClientBillables) p PIVOT (SUM(DollarsBilled) FOR SlipDates IN ([4/1/2007 12:00:00 AM], [5/1/2007 12:00:00 AM])) AS pvt
ORDER BY Client

If that change doesn't help, check the compatibility level on your database.

1. Open Microsoft SQL Server Management Studio
2. Select your database
3. Right-click the database and select Properties
4. Select Options on the left side of the window
5. Check that Compatibility Level near the top of the screen is set to SQL Server 2005 (90)

dprichard
Yak Posting Veteran

94 Posts

Posted - 2008-04-16 : 10:30:15

Okay, I ran this command

EXEC sp_dbcmptlevel 'timeslips', '90';

and didn't get any errors when I ran it, but I am still getting the error.

dprichard
Yak Posting Veteran

94 Posts

Posted - 2008-04-16 : 10:32:08

Bfoster. I just checked it and it does say SQL Server 2005 (90). Do I need to stop and start sql or something for it to take?

BTW, thanks ryan and bfost on the slipdates catch.

dprichard
Yak Posting Veteran

94 Posts

Posted - 2008-04-16 : 11:39:24

Okay, it is giving me the error, but when I click okay it gives me the results I want.

One more question. Instead of specifying the dates in here is there a way to say getdate() -12 months, then getdate() -11 months so I don't have to change the dates every month?

SELECT     Client, [4/1/2007 12:00:00 AM] AS Month1, [5/1/2007 12:00:00 AM] AS Month2, [6/1/2007 12:00:00 AM] AS Month3, [7/1/2007 12:00:00 AM] AS Month4, 
                      [8/1/2007 12:00:00 AM] AS Month5, [9/1/2007 12:00:00 AM] AS Month6, [10/1/2007 12:00:00 AM] AS Month7, [11/1/2007 12:00:00 AM] AS Month8, 
                      [12/1/2007 12:00:00 AM] AS Month9, [1/1/2008 12:00:00 AM] AS Month10, [2/1/2008 12:00:00 AM] AS Month11, [3/1/2008 12:00:00 AM] AS Month12, 
                      [4/1/2008 12:00:00 AM] AS Month13, Engineer
FROM         (SELECT     Client, DollarsBilled, SlipDates, Engineer
                       FROM          dbo.MonthlyClientBillables) p PIVOT (SUM(DollarsBilled) FOR SlipDates IN ([4/1/2007 12:00:00 AM], [5/1/2007 12:00:00 AM], 
                      [6/1/2007 12:00:00 AM], [7/1/2007 12:00:00 AM], [8/1/2007 12:00:00 AM], [9/1/2007 12:00:00 AM], [10/1/2007 12:00:00 AM], [11/1/2007 12:00:00 AM], 
                      [12/1/2007 12:00:00 AM], [1/1/2008 12:00:00 AM], [2/1/2008 12:00:00 AM], [3/1/2008 12:00:00 AM], [4/1/2008 12:00:00 AM])) AS pvt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-16 : 12:20:36

You have to do it dynamic if you want to pass getdate()-12,.. values as dates

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources