Dynamic Pivot Query

[Former Member]

Dear All,

I'm Bharathiraja, having 2 years of experience in SAP B1 functional.

Here i am posting some queries using dynamic pivot.

1.Total Sales Query with respective to customer and Item group.

DECLARE @listCol VARCHAR(8000)

DECLARE @Query VARCHAR(8000)

DECLARE @OINV VARCHAR(8000)

DECLARE @ORIN VARCHAR(8000)

DECLARE @From varchar(15)

DECLARE @To varchar(15)

Set @From =/* SELECT convert(varchar(15),Max(T0.DocDate),112) FROM OINM T0 WHERE T0.DocDate > */ convert(varchar(15),'[%0]',112)

Set @To =/* SELECT convert(varchar(15),Max(T0.DocDate),112) FROM OINM T0 WHERE T0.DocDate > */ convert(varchar(15),'[%1]',112)

SET @OINV =  isnull( STUFF((Select  ',' + convert(varchar(10),Docentry) from OINV

  where CANCELED <>'N' FOR XML PATH('')),1,1,'') ,0)

SET @ORIN =  isnull(STUFF((Select  ',' + convert(varchar(10),Docentry) from ORIN

  where CANCELED <>'N' FOR XML PATH('')),1,1,'') ,0)

SELECT  @listCol = STUFF(( SELECT DISTINCT '],[' + ltrim((ItmsGrpNam)) from OITB

ORDER BY '],[' + ltrim((ItmsgrpNam)) FOR XML PATH('') ), 1, 2, '') + ']'

SET @Query = 'Select *  from (Select T0.CardName,T4.ItmsGrpNam,sum(T1.LineTotal) as Sal from

OINV T0 Inner join INV1 T1 on T0.DocEntry = T1.DocEntry

Inner Join OFPR T2 on T2.AbsEntry = T0.FinncPriod

Inner Join OITM T3 Inner Join OITB T4 On T4.ItmsGrpCod = T3.ItmsgrpCod on T1.ItemCode = T3.ItemCode

Where convert(varchar(15),T0.Docdate,112) >= convert(varchar(15),'+@From+',112)

and convert(varchar(15),T0.Docdate,112) <= convert(varchar(15),'+@To+',112)

and t0.docentry not in ('+@OINV+')

group by T0.CardName ,T4.ItmsGrpNam

union all

Select T0.CardName,T4.ItmsGrpNam,sum(-T1.LineTotal) as Sal from

ORIN T0 Inner join RIN1 T1 on T0.DocEntry = T1.DocEntry

Inner Join OFPR T2 on T2.AbsEntry = T0.FinncPriod

Inner Join OITM T3 Inner Join OITB T4 On T4.ItmsGrpCod = T3.ItmsgrpCod on T1.ItemCode = T3.ItemCode

Where convert(varchar(15),T0.Docdate,112) >= convert(varchar(15),'+@From+',112)

and convert(varchar(15),T0.Docdate,112) <= convert(varchar(15),'+@To+',112)

and t0.docentry not in ('+@ORIN+')

group by T0.CardName ,T4.ItmsGrpNam

) src

PIVOT (sum(Sal) for ItmsgrpNam IN ('+@listCol+')) AS pvt'

EXECUTE (@Query)

Your Result will be like this

2. Total Sales Qty  with respective to customer and Item group

DECLARE @listCol VARCHAR(8000)

DECLARE @Query VARCHAR(8000)

DECLARE @OINV VARCHAR(8000)

DECLARE @ORIN VARCHAR(8000)

DECLARE @From varchar(15)

DECLARE @To varchar(15)

Set @From =/* SELECT convert(varchar(15),Max(T0.DocDate),112) FROM OINM T0 WHERE T0.DocDate > */ convert(varchar(15),'[%0]',112)

Set @To =/* SELECT convert(varchar(15),Max(T0.DocDate),112) FROM OINM T0 WHERE T0.DocDate > */ convert(varchar(15),'[%1]',112)

SET @OINV =  isnull( STUFF((Select  ',' + convert(varchar(10),Docentry) from OINV

  where CANCELED <>'N' FOR XML PATH('')),1,1,'') ,0)

SET @ORIN =  isnull(STUFF((Select  ',' + convert(varchar(10),Docentry) from ORIN

  where CANCELED <>'N' FOR XML PATH('')),1,1,'') ,0)

SELECT  @listCol = STUFF(( SELECT DISTINCT '],[' + ltrim((ItmsGrpNam)) from OITB

ORDER BY '],[' + ltrim((ItmsgrpNam)) FOR XML PATH('') ), 1, 2, '') + ']'

SET @Query = 'Select *  from (Select T0.CardName,T4.ItmsGrpNam,sum(T1.Quantity) as Sal from

OINV T0 Inner join INV1 T1 on T0.DocEntry = T1.DocEntry

Inner Join OITM T3 Inner Join OITB T4 On T4.ItmsGrpCod = T3.ItmsgrpCod on T1.ItemCode = T3.ItemCode

Where convert(varchar(15),T0.Docdate,112) >= convert(varchar(15),'+@From+',112)

and convert(varchar(15),T0.Docdate,112) <= convert(varchar(15),'+@To+',112)

and t0.docentry not in ('+@OINV+')

group by T0.CardName ,T4.ItmsGrpNam

union all

Select T0.CardName,T4.ItmsGrpNam,sum(-T1.Quantity) as Sal from

ORIN T0 Inner join RIN1 T1 on T0.DocEntry = T1.DocEntry

Inner Join OITM T3 Inner Join OITB T4 On T4.ItmsGrpCod = T3.ItmsgrpCod on T1.ItemCode = T3.ItemCode

Where convert(varchar(15),T0.Docdate,112) >= convert(varchar(15),'+@From+',112)

and convert(varchar(15),T0.Docdate,112) <= convert(varchar(15),'+@To+',112)

and t0.docentry not in ('+@ORIN+')

group by T0.CardName ,T4.ItmsGrpNam

) src

PIVOT (sum(Sal) for ItmsgrpNam IN ('+@listCol+')) AS pvt'

EXECUTE (@Query)

For above query you will get result like attached image

I

3.Stock in all the warehouse

declare @column as varchar(max)

declare @Query as varchar(max)

set @column= STUFF((Select '],['+whsname from OWHS FOR XML PATH('')),1,2,'') +']'

set @Query='select * from (select w.ItemCode,m.Itemname ,s.WhsName ,sum(w.OnHand) Stock

from oitw w inner join oitm m on m.itemcode=w.itemcode inner join OWHS s on s.WhsCode=w.WhsCode group by w.ItemCode,m.itemname ,s.WhsName) fg

Pivot (Sum(Stock) for

Whsname in ('+@column+')) AS pvtb'

EXECUTE (@Query)

For the above query you will get the following image result