Need help with additional SUM for SQL Pivot

Question

Need help with additional SUM for SQL Pivot

npeters 86796

Old Hand

Points: 310
More actions
December 10, 2010 at 8:46 pm

#388416

I currently have a table in this format:
DOCDATE | Ship | Vendor | PassengerCount | TrxFullPrice | TrxQuantity
In the Vendor Column I have a few vendors such as HOF & JHC
I have pivoted the table using this code.
SELECT *
FROM ( SELECT ship ,
passengercount ,
trxfullprice ,
docdate ,
vendor
FROM dbo.ICL_Sockeye_Flashreport
WHERE mediacompany = 'ppi'
AND docdate BETWEEN 'november 1 2010'
AND 'november 30 2010'
) f PIVOT ( SUM(TrxFullPrice) FOR Vendor IN ( hof, jhc ) ) p
ORDER BY ship
with this result:
ship | passengercount | docdate | hof | jhc
I have omitted the Column TrxQuantity because I need to SUM it as well in the pivot.
I am trying to get this result:
ship | passengercount | docdate | hof | TrxQuantity | Trx jhc | TrxQuantity
Any help would be appreciated as this is too complex for me to do & researching is making it worse.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95461 More actions · Answer 1

You need to look at the two articles in my signature that deal with Cross-tabs/Pivot Tables (parts 1 and 2) - I think that these will get you what you need.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 2

Also, if you post data as outlined in the article at the first link in my signature line below, you might even get someone to provide a tested, coded answer. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)