Pivot query for multiple columns

[krishnam_mathur]

Hi Team,

I need help with creating a pivot. Below is my query. I need all the values of Budget to be the columns, GL accounts in rows, and Monthly Budget, Monthly Actual, Variance for each Budget for each GLaccount.

I need a report like this

[azizelmir · Accepted solution]

Hi Krishnam,

Try this query:

Declare @month integer
Declare @Budget nvarchar(30)
Declare @startdate datetime
Declare @enddate datetime
set @month=1
set @startdate='01/01/2018'
set @enddate='04/30/2018'
set @Budget='Put here your Budget name'

SELECT T1.GLAccount,t1.ProfitCode,
		(case when t1.[MonthB]=1 then Sum(t1.MonthlyBudget) else 0 end) as 'Total Monthly Budget - JAN',
		(case when t1.[MonthB]=1 then Sum(t1.MonthlyActual) else 0 end) as 'Total Actual Budget - JAN',
		(case when t1.[MonthB]=1 then Sum(t1.MonthlyBudget) else 0 end)-(case when t1.[MonthB]=1 then Sum(t1.MonthlyActual) else 0 end) as 'Difference -JAN ',

		(case when t1.[MonthB]=2 then Sum(t1.MonthlyBudget) else 0 end) as 'Total Monthly Budget - FEB',
		(case when t1.[MonthB]=2 then Sum(t1.MonthlyActual) else 0 end) as 'Total Actual Budget - FEB',
		(case when t1.[MonthB]=2 then Sum(t1.MonthlyBudget) else 0 end)-(case when t1.[MonthB]=2 then Sum(t1.MonthlyActual) else 0 end) as 'Difference -FEB '

		,(case when t1.[MonthB]=3 then Sum(t1.MonthlyBudget) else 0 end) as 'Total Monthly Budget - MAR',
		(case when t1.[MonthB]=3 then Sum(t1.MonthlyActual) else 0 end) as 'Total Actual Budget - MAR',
		(case when t1.[MonthB]=3 then Sum(t1.MonthlyBudget) else 0 end)-(case when t1.[MonthB]=3 then Sum(t1.MonthlyActual) else 0 end) as 'Difference -MAR '

	,(case when t1.[MonthB]=4 then Sum(t1.MonthlyBudget) else 0 end) as 'Total Monthly Budget - APR',
		(case when t1.[MonthB]=4 then Sum(t1.MonthlyActual) else 0 end) as 'Total Actual Budget - APR',
		(case when t1.[MonthB]=4 then Sum(t1.MonthlyBudget) else 0 end)-(case when t1.[MonthB]=4 then Sum(t1.MonthlyActual) else 0 end) as 'Difference -APR '
 
---- the same for the remaining months

FROM
(SELECT t0.name as 'Budget', month(t3.RefDate) as 'MonthB', t3.ProfitCode ,
	t1.AcctCode as 'GLAccount',
	t2.DebLTotal as 'MonthlyBudget',
	sum(debit-credit) as 'MonthlyActual', 
	t2.DebLTotal-sum(debit-credit) as 'Difference' 
from obgs t0 inner join obgt t1 on t0.AbsId=t1.Instance inner join bgt1 t2 
	on t1.Instance=t2.Instance and t2.AcctCode=t1.AcctCode 
	and t2.Line_ID=@month left join jdt1 t3 on t1.AcctCode=t3.Account 
	and t3.RefDate between @startdate and @enddate 

GROUP BY t0.name,t1.AcctCode,t2.DebLTotal,t3.RefDate,t3.ProfitCode
) as T1
Group by T1.GlAccount, T1.MonthB, T1.ProfitCode

Thank you,

Aziz

[kothandaraman_nagarajan]

Hi,

Try this query,

declare @month integer declare @startdate datetime declare @enddate datetime set @month=0--'[%1]' set @startdate='01/01/2019'--/*t3.refdate=*/ '[%2]' set @enddate='01/31/2019'--/*t3.refdate=*/ '[%3]'

select GLAccount,[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]

from

(select t0.name as 'Budget', t1.AcctCode as 'GLAccount',t2.DebLTotal as 'MonthlyBudget',sum(debit-credit) as 'MonthlyActual', t2.DebLTotal-sum(debit-credit) as 'Difference',month(t3.RefDate) as Month from obgs t0 inner join obgt t1 on t0.AbsId=t1.Instance inner join bgt1 t2 on t1.Instance=t2.Instance and t2.AcctCode=t1.AcctCode and t2.Line_ID=@month left join jdt1 t3 on t1.AcctCode=t3.Account and t3.RefDate between @startdate and @enddate and t3.ProfitCode=t0.Name

group by t0.name,t1.AcctCode,t2.DebLTotal,t3.RefDate) S

PIVOT(SUM(MonthlyBudget) for MONTH in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

Regards,

Nagarajan

[krishnam_mathur]

Hi Nagarajan,

The format I need is

[azizelmir]

Hi Krishnam,

Could you provide us with your SQL-query ? do you want the output on EXCEL or through SQL pivot query ?

I would like also to ask you about the earlier post.png, did you got the solution ?

Thank you,

Aziz

[krishnam_mathur]

Hi Aziz,

The below query I am using

declare @month integer

declare @startdate datetime

declare @enddate datetime

set @month=0--'[%1]'

set @startdate='01/01/2019'--/*t3.refdate=*/ '[%2]'

set @enddate='01/31/2019'--/*t3.refdate=*/ '[%3]'

select t0.name as 'Budget', t1.AcctCode as 'GLAccount',t2.DebLTotal as 'MonthlyBudget',sum(debit-credit) as 'MonthlyActual', t2.DebLTotal-sum(debit-credit) as 'Difference' from obgs t0 inner join obgt t1 on t0.AbsId=t1.Instance inner join bgt1 t2 on t1.Instance=t2.Instance and t2.AcctCode=t1.AcctCode and t2.Line_ID=@month left join jdt1 t3 on t1.AcctCode=t3.Account and t3.RefDate between @startdate and @enddate and t3.ProfitCode=t0.Name

group by t0.name,t1.AcctCode,t2.DebLTotal

I need output in SQL query which I will run on SAP B1

azizelmir : The last issue was resolved thanks, i will close the same.

[krishnam_mathur]

azizelmir : any suggested solution..? I need help on this