Need help pivoting tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need help pivoting tables

Author

Topic

Darts75
Starting Member

27 Posts

Posted - 2014-08-07 : 02:11:29

Hi Everyone,

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


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.

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

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-07 : 08:00:38

Something like this:


select ItemCode, ItemName, Industry, SOH, TotalValue,
     , max(case when Duration = 'Qty Sold 1 Yr Ago' then QtySold) as 'Qty Sold 1 Yr Ago'
     , max(case when Duration = 'Qty Sold 2 Yrs Ago' then QtySold) as 'Qty Sold 2 Yrs Ago'
     , max(case when Duration = 'Qty Sold 3 Yrs Ago' then QtySold) as 'Qty Sold 3 Yrs Ago'
from ...
group by ItemCode, ItemName, Industry, SOH, TotalValue
...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources