The query work except the total sum of local amount. I can used sum IIF
or case when to calculate the sum of the local amount as usual but I would like to know how it works using a SQL Server pivot.
The first query are pivot and with roll up and I want this below query union to it and given the output of grant total.
DECLARE @financialYear INT;
DECLARE @financialPeriod INT;
SET @financialYear = 2013;
SET @financialPeriod = 7;
SELECT chartOfAccountCategoryDescription,
chartOfAccountDescription, coalesce([1], 0) AS [1], coalesce([2], 0) AS [2], coalesce([3], 0) AS [3], coalesce([4], 0) AS [4], coalesce([5], 0) AS [5] ,coalesce([6], 0) AS [6] ,coalesce([7], 0) AS [7] ,coalesce([8], 0) AS [8] ,coalesce([9], 0) AS [9] ,coalesce([10], 0) AS [10] ,coalesce([11], 0) AS [11] ,coalesce([12], 0) AS [12] ,coalesce([13], 0) AS [13] ,coalesce([14], 0) AS [14] ,coalesce([15], 0) AS [15] ,coalesce([16], 0) AS [16] ,coalesce([17], 0) AS [17] ,coalesce([18], 0) AS [18] ,coalesce([19], 0) AS [19] ,coalesce([20], 0) AS [20] ,coalesce([21], 0) AS [21] ,coalesce([22], 0) AS [22] ,coalesce([23], 0) AS [23] ,coalesce([24], 0) AS [24] ,coalesce([25], 0) AS [25] ,coalesce([26], 0) AS [26] ,coalesce([27], 0) AS [27] ,coalesce([28], 0) AS [28] ,coalesce([29], 0) AS [29] ,coalesce([30], 0) AS [30] ,coalesce([31], 0) AS [31] ,coalesce([Total], 0) AS [Total] FROM ( SELECT 'Total' AS chartOfAccountCategoryDescription ,' ' AS chartOfAccountDescription ,ISNULL(DATENAME(DAY, generalLedgerDate), 'Total') AS theMonth ,ABS(localAmount) AS items
FROM generalLedger
WHERE DATEPART(YEAR, generalLedgerDate) = @financialYear ) AS s
PIVOT(SUM(items) FOR theMonth IN (
[1] ,[2] ,[3] ,[4] ,[5] ,[6] ,[7] ,[8] ,[9] ,[10] ,[11] ,[12] ,[13] ,[14] ,[15] ,[16] ,[17] ,[18] ,[19] ,[20] ,[21] ,[22] ,[23] ,[24] ,[25] ,[26] ,[27] ,[28] ,[29] ,[30] ,[31] ,[Total] ))
AS p