SQL - Pivot with Grand Total Column and Row

Introduction

Microsoft SQL Server has introduced the PIVOT and UNPIVOT commands as enhancements to T-SQL with the release of Microsoft SQL Server 2005. Pivot Table in SQL has the ability to display data in custom aggregation. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output. UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

In this article, I’m concentrating on how to display Grand Total row and column for the pivot table as shown in the below grid view:

Here in the grid, I’m showing the number of matches played by a team in each month. At last, I need to show the grand total for each team (Last column) - this total gives the year count for the teams. In the same way, I need a grand total row as last row in grid, which will give the number of matches played by all the teams for that particular month.

Background

Usually as a .NET developer, first I will get the pivot table from the stored procedure to dataset and grand total row and column. I will manipulate in the C#/ VB code using Datatable (Datatable compute method for better performance). But in this article, I want to show how to get the grand total row and column from the stored procedure, so that we can directly display data in the grid without any manipulation in the C#/VB.

How It Works

Here I’m using pivot with dynamic columns, most questions were about the column list in the PIVOT statement. This list is fixed, but many times the new columns are determined by the report at a later stage. This problem is easily solved when we mix pivots with dynamic SQL, so here is a very simple script about how to dynamically generate the pivot statement:

Here in this stored procedure, I have used temporary table to get the grand total row. I did the UNION ALL with temporary table; don’t forget to drop the temporary table.

For any queries & suggestions, mail me @ narapareddy.shyam@gmail.com. By using some third party controls also we can achieve this, but here I concentrated only on typical/ normal SQL query. If anybody has any efficient and different ways, kindly share with me.

Once again thanks a lot to Mr.shyamprasad for this wonderful article.It helped me lot.
I have seen lot of peoples asked for db script and details .Here is the sample I have done with help of this article.

Thanks for the code it is exactly what i was looking for but I am having a problem with it. i already have some working code without the total col and rows. i tried to add this to it but now it just runs and doesn't produce any results. Could you please take a look and let me know what might be causing this?

SELECT * FROM
(SELECT Field1,Sum(Kgs) As SumOfKilos,[201049] As 201049,[201050] As 201050,[201101] As 201101 FROM Table1 Group By Field1) A
PIVOT
(
Sum(Pkgs)
FOR Field1
IN ([201049],[201050], [201101])
) B

I get a syntax error near 201049.................. i can't seem to go past it...Kindly assist......