Add Balance (Total) column to PIVOT

[elsenorjose]

Using SQL Server 2014, suppose I have a pivot query like so:

SELECT *
FROM (
SELECT
gl.reknr as [GL Account], oms25_0 as [Description], left(datename(month,docdate),13)as [Month],
bdr_hfl as Amount
FROM gbkmut gl left outer join grtbk acct on gl.reknr = acct.reknr
where docdate between '2016-01-01' and '2016-10-31'
)
as TB
PIVOT
(
SUM(Amount)
FOR [Month] IN (January, February, March, April,
May, June, July, August, September, October, November, December)
)
AS PVT
order by [GL Account] asc

How can I add 1 more column 'Balance' that is the total of every month's Amount? Basically a Trial Balance query with the YTD balance as a column unto itself?

Thank you.

 

[Olaf Doschke]

I think you get no answers, because it's quite impossible to just expand your code theoretically without having some data to play with.
Without that let me start and state it's easier to do the YTD calculations as afftermath of having the pivoted month sales.

What you want with one pivot is like asking whether grouping of a granularity of month is possible at the same time of grouping by quarter and years. The simple answer is no. You can of course do anything anyway, but it may require a totally different solution strategy or tool in your toolbelt, than trying to make this work with pivoting, eg the now not so new windowing functions are helpful to build up running totals - see

https://sqlperformance.com/2012/07/t-sql-queries/running-totals

Bye, Olaf.

 

[elsenorjose]

Hi Olaf,

I found the answer.

http://www.codeproject.com/Articles/232181/SQL-Pivot-with-Grand-Total-Column-and-Row

Jose