Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL PIVOT problem Expand / Collapse
Author
Message
Posted Monday, July 29, 2013 11:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 1:26 AM
Points: 18, Visits: 43
Hi

I'm getting the error message 'Incorrect syntax near PIVOT' when I try the following code as a view

SELECT year_, client, job, [Payroll] AS 'payr_perc', [Operational] AS 'oper_perc', [Administration] AS 'admin_perc', [Establishment] AS 'estab_perc',
[Financial] AS 'finan_perc', [Miscellaneous] AS 'misc_perc'
FROM (SELECT *
FROM dbo.budprepv_ExpAlloc_perc) ps PIVOT(SUM(Alloc_perc) FOR ExpHead IN ([Payroll], [Operational], [Administration], [Establishment],
[Financial], [Miscellaneous])) AS pvt

I can't see what's wronf! Could someone help me please?

Thanks

Dave
Post #1478678
Posted Monday, July 29, 2013 12:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 1:26 AM
Points: 18, Visits: 43
Is PIVOT not recognised in SQL 2000? Maybe its only, SQL 2005.....
Post #1478704
Posted Monday, July 29, 2013 12:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 3, 2014 12:32 PM
Points: 22, Visits: 143
That's correct. Pivot was introduced in SQL 2005.

You can use CASE to pivot in SQL 2000 (or all other versions for that matter). Here's an example:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/951ad7d4-d21e-4a13-9a5b-5d156fe3d90d/aggregated-case-expressions-versus-the-pivot-operator-is-one-better-than-the-other

Post #1478706
Posted Monday, July 29, 2013 12:45 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:04 AM
Points: 3,354, Visits: 7,256
Check out the following links
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1478708
Posted Monday, July 29, 2013 12:59 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 1:26 AM
Points: 18, Visits: 43
Thanks again.
Post #1478717
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse