# 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

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

### Assigned Tags

You must be Logged on to comment or reply to a post.

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

Regards

Unnikrishnan

Former Member
Blog Post Author

Thank you Unnikrishnan sir

Very useful information. This helps a lot.

Thanks,

Harshal

Former Member
Blog Post Author

Thank You Harshal

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)

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

Former Member
Blog Post Author

Hi Shahan,

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

Nice work!

Thanks,

Domenico

Very nice work, thanks !

Regards,

Johan