Skip to Content
Author's profile photo Former Member

Dynamic Pivot Query

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

Total Sale.png

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

Total Qty.pngI

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

Whs Stock.png

Assigned tags

      8 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Unnikrishnan Balan
      Unnikrishnan Balan

      Nice  work.....
      This will help lots of customers and consultants by sure.

      Regards

      Unnikrishnan

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thank you Unnikrishnan sir

      Author's profile photo Former Member
      Former Member

      Very useful information. This helps a lot.

      Thanks,

      Harshal

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thank You Harshal

      Author's profile photo Former Member
      Former Member

      Hi Raja

      could u pls help me on this one

      --------------------------------------------------------------------------------------

      Need query in Month wise consolidate and Dept wise

      This question is Not Answered.(Mark as assumed answered)

      Shahan ShamsGlass

      Hi experts

      i had created a SPT based on HEM1 Table

      pls find the SPT Below

      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      ALTER PROCEDURE [dbo].[Employee Vaccation Details based on HEM-1]

      (

      @LeaveFrom DateTime,

      @LeaveTo   DateTime

      )

      AS

      Begin

      SELECT OHEM.empID as 'Emp SAP ID',OHEM.U_EMPOLD_ID AS 'Emp File No',OHEM.firstName + ' ' + isNULL(OHEM.middleName,'') + ' ' + OHEM.lastName AS 'Employee Name',

             OUDP.Name AS 'Department',OHEM.U_DeptName AS 'Previous Dept',OHEM.U_IQProf as 'Iqama Profession',OHPS.name as 'Position',OCRY.Name as 'Citizenship',OHEM.U_IQId as 'Iqama ID',

             OHEM.passportNo as 'Passport No',OHEM.U_IQExDate as 'Iqama Exp Date',HEM1.U_SYear as 'Year',[@ABLEAVET].Name as 'Holiday Type',

             HEM1.fromDate as 'Leave Start Date',HEM1.toDate as 'Leave End Date', HEM1.U_ResumeDt AS 'Resume Date',

             HEM1.U_TotDays as 'Total days Leave Alloted',HEM1.reason as 'Any Reason' from OHEM

           

                      inner join OUDP on OHEM.dept=OUDP.Code

                      inner join OHPS on OHEM.position=OHPS.posID

                      inner join OCRY on OHEM.citizenshp=OCRY.Code

                      inner join HEM1 on OHEM.empID=HEM1.empID

                      inner join [@ABLEAVET] on HEM1.U_LeaveID=[@ABLEAVET].Code

      Where OHEM.dept not in (47,49) and HEM1.fromDate between @LeaveFrom and @LeaveTo AND HEM1.reason = 'Vacation'

      END

      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      i need this in the below given format

      company name     Type of Actions       Jan   -      Feb    -      Mar     -      Apr        -       May       -       June

      ABCD                   Vaccation                1              0                1           5                     6                    0

                                   Terminiation             5               0              0             5                   10                   15

      -------------------------------------------------------------------------------------------------------------------------------------------------------------------

      EFGH                   Vaccation               0                  1            6              5                   15                   25

                              

                                   Termination           10                5               0             0                   0                     1

      ----------------------------------------------------------------------------------------------------------------------------------------------------------------------

      filtering is not needed

      also in the same query i need to attach the Terminated Employees count in the same format

      Regards

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Shahan,

      Use sub query for months and in where condition include your termination reason also

      Author's profile photo Domenico Lovino
      Domenico Lovino

      Nice work!

      Thanks,

      Domenico

      Author's profile photo Johan Hakkesteegt
      Johan Hakkesteegt

      Very nice work, thanks !

      Regards,

      Johan