Need help pivoting tables
-
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
-
Hi Dave,
Luckily SQL holds a Pivot and unpivot function that will do the trick nicely.
if you follow this link then it will give you the details that you need. http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
I have also mocked up an example relevant to you that you can use as a guide:
Create table #temp
(QTYSold int,
Period Varchar(30)
)
insert into #temp
(QTYSold, Period)
Values (10 ,'Qty Sold 2 Yrs Ago'),
(22 ,'Qty Sold 2 Yrs Ago'),
(34 ,'Qty Sold 1 Yrs Ago'),
(9 ,'Qty Sold 1 Yrs Ago'),
(12 ,'Qty Sold 1 Yrs Ago'),
(45 ,'Qty Sold 3 Yrs Ago'),
(65 ,'Qty Sold 3 Yrs Ago'),
(25 ,'Qty Sold 3 Yrs Ago'),
(13 ,'Qty Sold 1 Yrs Ago'),
(7 ,'Qty Sold 2 Yrs Ago')
select [Qty Sold 1 Yrs Ago], [Qty Sold 2 Yrs Ago], [Qty Sold 3 Yrs Ago]
from (select QTYSold, Period from #temp) as sourcetable
pivot (sum(QTYSold)
for Period in ([Qty Sold 1 Yrs Ago], [Qty Sold 2 Yrs Ago], [Qty Sold 3 Yrs Ago]))as PivotTable
-
try following query
drop table ttt
-----------------------
SELECT
T0.ItemCode
, T0.ItemName
, T0.U_SCE_IN_Industry
, T0.OnHand
, T0.StockValue
,sum(t1.quantity) dd
, 'Qty Sold ' + convert(varchar,datediff(year,(t2.docdate),getdate())) + ' Yr Ago' dsd
into ttt
FROM dbo.OITM T0
LEFT JOIN dbo.AU_SALES_R T1 ON T1.ItemCode = T0.ItemCode --COLLATE SQL_Latin1_General_CP850_CI_AS
left JOIN dbo.AU_SALES_H T2 ON T2.DocEntry = T1.DocEntry
WHERE T2.DocDate >= (select min(docdate) from au_sales_h)
AND T2.DocDate <= (select max(docdate) from au_sales_h)
GROUP BY T0.ItemCode, T0.ItemName, T0.U_SCE_IN_Industry, T0.OnHand, T0.StockValue,docdate
------------------------
declare @mrows int,@mstring varchar(3999),@mflag varchar(1)
set @mrows = 0
set @mflag = 'T'
while @mrows < (select count(*) from dbo.AU_SALES_H t2)
begin
set @mrows = @mrows + case when @mflag = 'T' then 0 else 1 end
if @mrows > (select count(*)
from dbo.AU_SALES_H t2)
begin
set @mflag = 'F'
break
end
else
begin
print @mrows
set @mstring = 'alter table ttt add Qty_Sold_' + convert(varchar,@mrows) + '_Yr_Ago numeric(18,2) '
exec (@mstring)
set @mstring = 'update ttt set Qty_Sold_' + convert(varchar,@mrows) + '_Yr_Ago = dd where dsd = '
+ '''Qty Sold ' + convert(varchar,@mrows) + ' Yr Ago'''
exec (@mstring)
print @mstring
set @mflag = 'F'
continue
end
end
select ItemCode, ItemName, U_SCE_IN_Industry, OnHand, StockValue,
sum(Qty_Sold_0_Yr_Ago) Qty_Sold_0_Yr_Ago, sum(Qty_Sold_1_Yr_Ago) Qty_Sold_1_Yr_Ago,
sum(Qty_Sold_2_Yr_Ago) Qty_Sold_2_Yr_Ago,sum( Qty_Sold_3_Yr_Ago) Qty_Sold_3_Yr_Ago,
sum( Qty_Sold_4_Yr_Ago) Qty_Sold_4_Yr_Ago
from ttt group by ItemCode, ItemName, U_SCE_IN_Industry, OnHand, StockValue
-
as per your requirement following is the query of pivot but could not sum the quantity this is as per your requirement.
SELECT ItemCode AS 'Item Code',ItemName AS 'Item Name'
, U_SCE_IN_Industry AS 'Industry'
, OnHand AS 'SOH'
, StockValue AS 'Total Value'
,[Qty Sold 0 Yr Ago]
,[Qty Sold 1 Yr Ago]
,[Qty Sold 2 Yr Ago]
,[Qty Sold 3 Yr Ago]
,[Qty Sold 4 Yr Ago]
--,docentry as docen
FROM
(
SELECT
T0.ItemCode
, T0.ItemName
, T0.U_SCE_IN_Industry
, T0.OnHand
, T0.StockValue
, ISNULL(CAST(CAST(SUM(T1.Quantity) AS decimal(10,2)) AS varchar), '') AS 'QUANTITY'
, 'Qty Sold ' + convert(varchar,datediff(year,(t2.docdate),getdate())) + ' Yr Ago' dsd
FROM dbo.OITM T0
LEFT JOIN dbo.AU_SALES_R T1 ON T1.ItemCode = T0.ItemCode --COLLATE SQL_Latin1_General_CP850_CI_AS
INNER JOIN dbo.AU_SALES_H T2 ON T2.DocEntry = T1.DocEntry
WHERE T2.DocDate >= (select min(docdate) from au_sales_h)
--@1yrEnd
AND T2.DocDate <= (select max(docdate) from au_sales_h) --@1yrBegin
GROUP BY T0.ItemCode, T0.ItemName, T0.U_SCE_IN_Industry, T0.OnHand, T0.StockValue,docdate--,t1.docentry
) AS p
PIVOT
(
COUNT(QUANTITY)
FOR dsd IN
( [Qty Sold 0 Yr Ago], [Qty Sold 1 Yr Ago], [Qty Sold 2 Yr Ago], [Qty Sold 3 Yr Ago], [Qty Sold 4 Yr Ago] )
) AS pvt
-
Using some guesses, this might help you to read the table just once and do the pivot at once.
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(CASE WHEN T2.DocDate >= DATEADD(YY, - 1, GETDATE()) AND GETDATE() THEN T1.Quantity END) AS DECIMAL(10, 2)) AS VARCHAR), '') AS 'Qty Sold 1 Yr Ago'
,ISNULL(CAST(CAST(SUM(CASE WHEN T2.DocDate >= DATEADD(YY, - 2, GETDATE()) AND T2.DocDate < DATEADD(YY, - 1, GETDATE()) THEN T1.Quantity END) AS DECIMAL(10, 2)) AS VARCHAR), '') AS 'Qty Sold 2 Yr Ago'
,ISNULL(CAST(CAST(SUM(CASE WHEN T2.DocDate >= DATEADD(YY, - 3, GETDATE()) AND T2.DocDate < DATEADD(YY, - 2, GETDATE()) THEN T1.Quantity END) AS DECIMAL(10, 2)) AS VARCHAR), '') AS 'Qty Sold 3 Yr Ago'
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 >= DATEADD(YY, - 3, GETDATE())
AND T2.DocDate <= GETDATE()
GROUP BY T0.ItemCode
,T0.ItemName
,T0.U_SCE_IN_Industry
,T0.OnHand
,T0.StockValue
ORDER BY CB.[Item Code]
,CB.Duration
Here's a reference to cross tabs approach: http://www.sqlservercentral.com/articles/T-SQL/63681/
-
Hi jonidotcodotuk,
Your generous advice has helped to set me on the right track. Thank you.
Kind Regards,
David
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply