Post reply

Crazy pivot tables

  • 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

  • January 3, 2007 at 4:32 pm

    #681099

    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

  • January 4, 2007 at 8:19 am

    #681212

    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 2 (of 2 total)

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