Skip to Content

Pivot query for multiple columns

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

capture.png (40.4 kB)
capture.png (65.4 kB)
Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Best Answer
    Posted on Feb 20, 2019 at 08:45 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Feb 15, 2019 at 07:42 AM

    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


    post.png (34.5 kB)
    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Feb 16, 2019 at 03:36 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.