SQL PIVOT problem

• Davebhoy

  SSC Eights!

  Points: 858

  More actions

  July 29, 2013 at 11:24 am

  #99850

  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

• Davebhoy

  SSC Eights!

  Points: 858

  More actions

  July 29, 2013 at 12:25 pm

  #1636737

  Is PIVOT not recognised in SQL 2000? Maybe its only, SQL 2005.....

• Terry Troisi

  SSC-Addicted

  Points: 430

  More actions

  July 29, 2013 at 12:29 pm

  #1636739

  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

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  July 29, 2013 at 12:45 pm

  #1636741

  Check out the following links

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url]

  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/url]

  Luis C.
  General Disclaimer:
  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?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Davebhoy

  SSC Eights!

  Points: 858

  More actions

  July 29, 2013 at 12:59 pm

  #1636749

  Thanks again.