Need help pivoting tables

Question

Post reply

Need help pivoting tables

david.dartnell

Ten Centuries

Points: 1276
More actions
August 7, 2014 at 12:11 am

#288890

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

Viewing 6 posts - 1 through 5 (of 5 total)

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

BI_Baracus SSC-Addicted Points: 465 More actions · Answer 1

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

rdinesh14 Valued Member Points: 55 More actions · Answer 2

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

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

rdinesh14 Valued Member Points: 55 More actions · Answer 3

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

Luis Cazares SSC Guru Points: 183686 More actions · Answer 4

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://qa.sqlservercentral.com/articles/T-SQL/63681/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

david.dartnell Ten Centuries Points: 1276 More actions · Answer 5

david.dartnell

Ten Centuries

Points: 1276

August 8, 2014 at 12:50 am

#1736304

Hi jonidotcodotuk,

Your generous advice has helped to set me on the right track. Thank you.

Kind Regards,

David