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

Grand totals (or any other intermediate total) with pivot queries are very simple, once you have learnt the correct technique for writing pivot queries. Which in your case means that you need to unlearn one thing first: forget that you ever saw the PIVOT operator.

Because I've never learnt the PIVOT operator, I don't really know what your code does (it also suffers from readability problems), but here is a simple pivot query that runs in the Northwind database:

SELECT E.LastName,
       [1996] = SUM(CASE Year(OrderDate) WHEN '1996' THEN 1 ELSE 0 END),
       [1997] = SUM(CASE Year(OrderDate) WHEN '1997' THEN 1 ELSE 0 END),
       [1998] = SUM(CASE Year(OrderDate) WHEN '1998' THEN 1 ELSE 0 END)
FROM   Orders O
JOIN   Employees E ON O.EmployeeID = E.EmployeeID
GROUP  BY E.LastName

This particular query gives the number of orders per year for each employee.

The technique is that we use the aggregate we want and a CASE expression to filter out the values we want. If we don't really want any aggregate at all, but just pivot scalar values we use MIN or MAX for the aggregation.

Once you have this going, you can easily add all sorts of other columns. For instance:

SELECT E.LastName,
       [1996] = SUM(CASE Year(OrderDate) WHEN '1996' THEN 1 ELSE 0 END),
       [1997] = SUM(CASE Year(OrderDate) WHEN '1997' THEN 1 ELSE 0 END),
       [1998] = SUM(CASE Year(OrderDate) WHEN '1998' THEN 1 ELSE 0 END),
       Dec    = SUM(CASE datepart(Month, OrderDate) WHEN 12 THEN 1 ELSE 0)
 END),
       GrantTotal = COUNT(*)
FROM   Orders O
JOIN   Employees E ON O.EmployeeID = E.EmployeeID
GROUP  BY E.LastName

My point is  i want maximize potential SQL Server.As i said before, i can do it via CASE WHEN or using SUM(IIF( but  i want to knew how pivot handle it ?

The most odd is 

ISNULL(DATENAME(DAY, generalLedgerDate), 'Total') AS theMonth 

only work upon(main query which will be union to grant total query)

 GROUP BY chartOfAccountCategoryDescription,
 CONCAT (chartOfAccountNumber,
 CONCAT (' - ',chartOfAccountDescription)),DATENAME(DAY, generalLedgerDate)    
WITH ROLLUP    
HAVING GROUPING
(CONCAT (chartOfAccountNumber,CONCAT (' - ',chartOfAccountDescription))) = 0

** It just a simple sum total  like this but i would like to knew how pivot do it

SELECT SUM(IIF(day=1,localAmount),0) as [1], .. continue till 31 or using eom SUM(localAmount) as total FROM generalledger

** I see some e.g in  the internet push the main query  and sum it up at temporarily table. But it seem to way complicated just to get grand total figure.

If you want to maximize SQL Server, you won't do pivots in SQL Server at all. Pivots are pure display functionality which, in a tiered architecture, should happen in the app/display tier.

Yes, there are time when it's faster and easier to knock out the pivot in SQL, so we've all done it... But in general, it's a bad habit to get into.

I equate it to the saw blade on a Swiss army knife. Super handy if you have something small to knock out quickly... but it's not going to be a good replacement for a real handsaw...

Don't use pivot ? Do explain please ?

I want to do it because I'm output data  total/grant total directly via Excel Add-in ..The customer prefer excel instead of web based reporting tools.

You loose flexibility as soon as you pivot the data in SQL, especially if you're connecting it to Excel.

Excel has a beautiful feature called PowerPivot and it's infinity more useful than a static, pre-pivoted, data pump.

Letting PowerPivot (or PowerView) handle the pivoting, gives your end user tons of flexibility to slice, dice, filter & view the data however they wish (as opposed to calling you every time they want to see something "just a little bit differently").

The only circumstance that comes to mind, where I'd pivot in SQL Server instead of Excel, is if the un-pivoted/un-aggregated data set was simply too large for Excel to handle.

HTH,

Jason

Just for the record... If you do decide that you still want to do the pivot in SQL, I'd recommend the approach proposed by Erland.

Here's a thread I started a few years ago, back when I still had questions about which was the better approach...

Aggregated CASE expressions versus the PIVOT operator Is one better than the other?

Power Pivot For SQL Server in Excel don't have security future. The only easiest to me is filter out via excel plugin (xll) and query unnecessary for limitation 1 million record per sheet problem.

I dono why is different while query both either from sql server no limit row and normal excel got limit row 1 million. .