Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2014
    Posts
    35

    Unanswered: Need help pivoting tables?

    Hi Everyone,

    I have the following query which I would like to Pivot...

    Code:
    DECLARE @1yrBegin DATE, @1yrEnd DATE
    		, @2yrBegin DATE, @2yrEnd DATE
    		, @3yrBegin DATE, @3yrEnd DATE
    
    SET @1yrBegin = GETDATE()
    SET @1yrEnd = DATEADD(YY, -1, GETDATE())
    
    SET @2yrBegin = @1yrEnd
    SET @2yrEnd = DATEADD(YY, -2, GETDATE())
    
    SET @3yrBegin = @2yrEnd
    SET @3yrEnd = DATEADD(YY, -3, GETDATE())
    
    SELECT *
    
    FROM
    (
    
    /* Cube date: Today - 1 year to Today */
    
    SELECT
    T0.ItemCode AS 'Item Code'
    , T0.ItemName AS 'Item Name'
    , T0.U_SCE_IN_Industry AS 'Industry'
    , T0.OnHand AS 'SOH'
    , T0.StockValue AS 'Total Value'
    , ISNULL(CAST(CAST(SUM(T1.Quantity) AS decimal(10,2)) AS varchar), '') AS 'Qty Sold'
    , 'Qty Sold 1 Yr Ago' AS 'Duration'
    
    FROM AU.dbo.OITM T0
    LEFT JOIN SCE.dbo.AU_SALES_R T1 ON T1.ItemCode = T0.ItemCode COLLATE SQL_Latin1_General_CP850_CI_AS
    INNER JOIN SCE.dbo.AU_SALES_H T2 ON T2.DocEntry = T1.DocEntry
    
    WHERE T2.DocDate >= @1yrEnd AND T2.DocDate <= @1yrBegin
    
    GROUP BY T0.ItemCode, T0.ItemName, T0.U_SCE_IN_Industry, T0.OnHand, T0.StockValue
    
    UNION ALL
    
    /* Cube data: Today - 2 years to Today - 1 year */
    
    SELECT
    T0.ItemCode AS 'Item Code'
    , T0.ItemName AS 'Item Name'
    , T0.U_SCE_IN_Industry AS 'Industry'
    , T0.OnHand AS 'SOH'
    , T0.StockValue AS 'Total Value'
    , ISNULL(CAST(CAST(SUM(T1.Quantity) AS decimal(10,2)) AS varchar), '') AS 'Qty Sold'
    , 'Qty Sold 2 Yrs Ago' AS 'Duration'
    
    FROM AU.dbo.OITM T0
    LEFT JOIN SCE.dbo.AU_SALES_R T1 ON T1.ItemCode = T0.ItemCode COLLATE SQL_Latin1_General_CP850_CI_AS
    INNER JOIN SCE.dbo.AU_SALES_H T2 ON T2.DocEntry = T1.DocEntry
    
    WHERE T2.DocDate >= @2yrEnd AND T2.DocDate <= @2yrBegin
    
    GROUP BY T0.ItemCode, T0.ItemName, T0.U_SCE_IN_Industry, T0.OnHand, T0.StockValue
    
    UNION ALL
    
    /* Cube data: Today - 3 years to Today - 2 years */
    
    SELECT
    T0.ItemCode AS 'Item Code'
    , T0.ItemName AS 'Item Name'
    , T0.U_SCE_IN_Industry AS 'Industry'
    , T0.OnHand AS 'SOH'
    , T0.StockValue AS 'Total Value'
    , ISNULL(CAST(CAST(SUM(T1.Quantity) AS decimal(10,2)) AS varchar), '') AS 'Qty Sold'
    , 'Qty Sold 3 Yrs Ago' AS 'Duration'
    
    FROM AU.dbo.OITM T0
    LEFT JOIN SCE.dbo.AU_SALES_R T1 ON T1.ItemCode = T0.ItemCode COLLATE SQL_Latin1_General_CP850_CI_AS
    INNER JOIN SCE.dbo.AU_SALES_H T2 ON T2.DocEntry = T1.DocEntry
    
    WHERE T2.DocDate >= @3yrEnd AND T2.DocDate <= @3yrBegin
    
    GROUP BY T0.ItemCode, T0.ItemName, T0.U_SCE_IN_Industry, T0.OnHand, T0.StockValue
    
    ) AS CB
    
    ORDER BY CB.[Item Code], CB.Duration
    Below is a capture of some sample data returned by my query, and furthermore how I would like it to be presented.

    http://i62.tinypic.com/nb58bn.jpg

    As shown in the image above I would like to do away with the current 'Duration' column, and pivot my data such that the 'Qty Sold' is listed horizontally (rather than vertically) under the corresponding headings, 'Qty Sold 1 Yr Ago', 'Qty Sold 2 Yrs Ago', and 'Qty Sold 3 Yrs Ago'.

    Given that I know I am looking at only three years of past sales, and by extension only 3 rows being pivoted I figure that a static pivot should apply here. However as I am relatively new to SQL I am at a loss concerning how to perform the actual pivot.

    Any help here will be greatly appreciated.

    Kind Regards,

    David

  2. #2
    Join Date
    Jan 2003
    Location
    Minneapolis
    Posts
    58
    Made some modifications, but try this:

    Code:
    DECLARE @1yrBegin DATE, @1yrEnd DATE
    		, @2yrBegin DATE, @2yrEnd DATE
    		, @3yrBegin DATE, @3yrEnd DATE
    
    SET @1yrBegin = GETDATE()
    SET @1yrEnd = DATEADD(YY, -1, GETDATE())
    
    SET @2yrBegin = @1yrEnd
    SET @2yrEnd = DATEADD(YY, -2, GETDATE())
    
    SET @3yrBegin = @2yrEnd
    SET @3yrEnd = DATEADD(YY, -3, GETDATE())
    
    SELECT [Item Code], [Item Name], Industry, SOH, [Total Value], [Qty Sold 1 Yr Ago], [Qty Sold 2 Yrs Ago], [Qty Sold 3 Yrs Ago]
    
    FROM
    (
    
    /* Cube date: Today - 1 year to Today */
    
    SELECT
    T0.ItemCode AS 'Item Code'
    , T0.ItemName AS 'Item Name'
    , T0.U_SCE_IN_Industry AS 'Industry'
    , T0.OnHand AS 'SOH'
    , T0.StockValue AS 'Total Value'
    , ISNULL(CAST(CAST(SUM(T1.Quantity) AS decimal(10,2)) AS varchar), '') AS QtySold
    , 'Qty Sold 1 Yr Ago' AS 'Duration'
    
    FROM AU.dbo.OITM T0
    LEFT JOIN SCE.dbo.AU_SALES_R T1 ON T1.ItemCode = T0.ItemCode COLLATE SQL_Latin1_General_CP850_CI_AS
    INNER JOIN SCE.dbo.AU_SALES_H T2 ON T2.DocEntry = T1.DocEntry
    
    WHERE T2.DocDate >= @1yrEnd AND T2.DocDate <= @1yrBegin
    
    GROUP BY T0.ItemCode, T0.ItemName, T0.U_SCE_IN_Industry, T0.OnHand, T0.StockValue
    
    UNION ALL
    
    /* Cube data: Today - 2 years to Today - 1 year */
    
    SELECT
    T0.ItemCode AS 'Item Code'
    , T0.ItemName AS 'Item Name'
    , T0.U_SCE_IN_Industry AS 'Industry'
    , T0.OnHand AS 'SOH'
    , T0.StockValue AS 'Total Value'
    , ISNULL(CAST(CAST(SUM(T1.Quantity) AS decimal(10,2)) AS varchar), '') AS QtySold
    , 'Qty Sold 2 Yrs Ago' AS 'Duration'
    
    FROM AU.dbo.OITM T0
    LEFT JOIN SCE.dbo.AU_SALES_R T1 ON T1.ItemCode = T0.ItemCode COLLATE SQL_Latin1_General_CP850_CI_AS
    INNER JOIN SCE.dbo.AU_SALES_H T2 ON T2.DocEntry = T1.DocEntry
    
    WHERE T2.DocDate >= @2yrEnd AND T2.DocDate <= @2yrBegin
    
    GROUP BY T0.ItemCode, T0.ItemName, T0.U_SCE_IN_Industry, T0.OnHand, T0.StockValue
    
    UNION ALL
    
    /* Cube data: Today - 3 years to Today - 2 years */
    
    SELECT
    T0.ItemCode AS 'Item Code'
    , T0.ItemName AS 'Item Name'
    , T0.U_SCE_IN_Industry AS 'Industry'
    , T0.OnHand AS 'SOH'
    , T0.StockValue AS 'Total Value'
    , ISNULL(CAST(CAST(SUM(T1.Quantity) AS decimal(10,2)) AS varchar), '') AS QtySold
    , 'Qty Sold 3 Yrs Ago' AS 'Duration'
    
    FROM AU.dbo.OITM T0
    LEFT JOIN SCE.dbo.AU_SALES_R T1 ON T1.ItemCode = T0.ItemCode COLLATE SQL_Latin1_General_CP850_CI_AS
    INNER JOIN SCE.dbo.AU_SALES_H T2 ON T2.DocEntry = T1.DocEntry
    
    WHERE T2.DocDate >= @3yrEnd AND T2.DocDate <= @3yrBegin
    
    GROUP BY T0.ItemCode, T0.ItemName, T0.U_SCE_IN_Industry, T0.OnHand, T0.StockValue
    
    ) AS CB
    
    PIVOT
    (
    SUM(QtySold) FOR Duration IN ([Qty Sold 1 Yr Ago], [Qty Sold 2 Yrs Ago], [Qty Sold 3 Yrs Ago])
    ) AS pvt
    
    ORDER BY [Item Code], Duration

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •