Crazy pivot tables
-
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
-
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 -
Thank you very much. I need to investigate the syntax of the pivot command more. for some reason this didn't click yesterday
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply