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: Thursday, July 31, 2014 9:58 AM
Points: 18, Visits: 44
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: Thursday, July 31, 2014 9:58 AM
Points: 18, Visits: 44
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 @ 2:04 PM
Points: 3,572, Visits: 8,008
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.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

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: Thursday, July 31, 2014 9:58 AM
Points: 18, Visits: 44
Thanks again.
Post #1478717
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse