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