SQL Pivot table

Posted on 2012-09-21
Last Modified: 2012-10-01
I am working in SQL Server 2008 and trying to pivot some data.  SQL is telling me that I have incorrect syntax near 'NetWorth'.  I'm not seeing it.  Does anyone out there see it?  When I execute the code without the PIVOT the select does work.

Thanks in advance
Question by:RichNH
    LVL 17

    Accepted Solution

    Wrap the 'NetWorth' and all the other entries in [] square brackets instead of single quotes
    LVL 1

    Author Comment

    Thank you BCUNNEY, that fixed part of it.  Apparently you also need to reference the fields from the pivot instead of fields from the table *RJZ_Financials because it wasn't recognizing the fields I had in the select.  But when I referenced the PVT fields, that worked.  

    However there is still one field it doesn't recognize and I can't figure out why, FD.VariableName is in the same set as FD.ValueUSD but the editor isn't recognizing it as a valid field.

    In this code:
    MAX(FD.ValueUSD) FOR FD.VariableName IN  
    it accepts FD.ValueUSD but doesn't accept FD.VariableName.

    The FD.VariableName has a red squiggly line under it.  What am I doing wrong?

    I have reattached the code with the corrections so far.
    LVL 17

    Expert Comment

    by:Barry Cunney
    See if you can first get the INNER query(FD) and the OUTER APPLY part working on it's own - forget about the PIVOT for the moment - like see if you can build the OUTER apply into the FD query set first and then if this works do the pivoting afterwards in the outer query
    LVL 1

    Author Comment

    As it is, I got the whole thing to work,  sorry for the delay, month end you know.  Here's your points and thanks for your help.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now