PIVOT Syntax HELP....!

Posted on 2008-11-19
Last Modified: 2012-05-05

Can someone eyeball this SQL which uses PIVOT and help me with the error? It works find until I try to ADD more that ONE field to the SUM in the PIVOT..???

--Weekly Reserve Data
select distinct Portfolio_wk
 , count( [Orderid]) as OrderCount
 , sum(case when [Customer?] = 'Customer' then 1 else 0 end) as CustomerCount
 , [1] as [2008-10-20]
 , [2] as [2008-10-27]
 , [3] as [2008-11-03]
 , [4] as [2008-11-10]
 , [5] as [2008-11-17]
 , [6] as [2008-11-24]  -- <== Add weeks here to increase the columns
  (select portfolio_wk,orderid, customerid, isnull(cogsamount,0) as cogsamount, [customer?], cogswp, cogsrca from dbo.reserve) r
(sum(cogsamount) for cogsamount  in ([1],[2],[3],[4],[5],[6]))  -- <--THIS WORKS
(sum(cogsamount + cogswp + cogsrca) for cogsamount  in ([1],[2],[3],[4],[5],[6])) -- <--THIS DOES NOT
as pvt
group by portfolio_wk, pvt.[1], pvt.[2], pvt.[3], pvt.[4], pvt.[5], pvt.[6]

Basically I need to have a SUM of the 3 columns cogsamount, cogswp, cogsrca


Question by:MIKE
    LVL 17

    Author Comment

    ooops...sorry here is the error:

    Msg 102, Level 15, State 1, Line 15
    Incorrect syntax near '+'.
    LVL 17

    Author Comment

    I forgot to comment out ONE of those lines referenced..but hopefully you'll understand what i mean....

    How do I combine 3 columns of data and SUM them for the PIVOT...???
    LVL 40

    Accepted Solution


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    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…
    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    734 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

    23 Experts available now in Live!

    Get 1:1 Help Now