[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

PIVOT Syntax HELP....!

Posted on 2008-11-19
3
Medium Priority
?
461 Views
Last Modified: 2012-05-05
Experts:

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
from
  (select portfolio_wk,orderid, customerid, isnull(cogsamount,0) as cogsamount, [customer?], cogswp, cogsrca from dbo.reserve) r
PIVOT
(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

AWARDING POINTS FAST....

thanks
M
0
Comment
Question by:MIKE
  • 2
3 Comments
 
LVL 17

Author Comment

by:MIKE
ID: 22999244
ooops...sorry here is the error:

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

Author Comment

by:MIKE
ID: 22999309
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...???
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 22999927
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
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 extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

830 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