Crazy pivot tables

Question

Post reply

Crazy pivot tables

James Riehl

Ten Centuries

Points: 1154

January 3, 2007 at 3:09 pm

#171433

I have an appropriate pivot query for my report that gets the sumation of all items and puts them into these columns.

lineItem	Jan	Feb	Mar	Apr	...	Dec
item1	1	2	3	4	...	12
item2	2	4	6	8	...	24

I need to get the following

lineItem	Jan	Feb	Mar	Apr	...	Dec	YTD
item1	1	2	3	4	...	12	78
item2	2	4	6	8	...	24	156

Is this possible?

My querry looks something like the following

WITH ProductSales(ProductID, OrderYear, OrderTotal)
 AS (
 SELECT
 det.productID,
 YEAR(hdr.orderdate),
 det.linetotal
 FROM sales.salesorderdetail det
 JOIN sales.salesorderheader hdr
 ON det.salesorderid = hdr.salesorderid
 )
SELECT
 ProductSalesPivot.productID,
 Total_Sales_2001 = ISNULL([2001], 0),
 Total_Sales_2002 = ISNULL([2002], 0),
 Total_Sales_2003 = ISNULL([2003], 0),
 Total_Sales_2004 = ISNULL([2004], 0)
 FROM ProductSales
 PIVOT ( SUM(OrderTotal)
 FOR OrderYear IN ([2001], [2002], [2003], [2004])
) AS ProductSalesPivot
ORDER BY
 ProductSalesPivot.ProductID

I'd like to have a summary column that sums all elements of that row.
Is this possible? Thanks in advance for your help

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

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

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

WITH ProductSales(ProductID, OrderYear, OrderTotal)

AS (

SELECT

det.productID,

YEAR(hdr.orderdate),

det.linetotal

FROM sales.salesorderdetail det

JOIN sales.salesorderheader hdr

ON det.salesorderid = hdr.salesorderid

)

SELECT

ProductSalesPivot.productID,

Total_Sales_2001 = ISNULL([2001], 0),

Total_Sales_2002 = ISNULL([2002], 0),

Total_Sales_2003 = ISNULL([2003], 0),

Total_Sales_2004 = ISNULL([2004], 0),

Total_Sales = IsNull(ISNULL([2001], 0) + ISNULL([2002], 0) + ISNULL([2003], 0) + ISNULL([2004], 0), 0)

FROM ProductSales

PIVOT ( SUM(OrderTotal)

FOR OrderYear IN ([2001], [2002], [2003], [2004])

) AS ProductSalesPivot

ORDER BY

ProductSalesPivot.ProductID

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

James Riehl Ten Centuries Points: 1154 More actions · Answer 2

Thank you very much. I need to investigate the syntax of the pivot command more. for some reason this didn't click yesterday 🙂