Click here to Skip to main content
15,867,704 members
Articles / Database Development / SQL Server

SQL - Pivot with Grand Total Column and Row

Rate me:
Please Sign up or sign in to vote.
4.76/5 (22 votes)
16 Aug 2018CPOL3 min read 199.2K   66   33
SQL Dynamic Pivots

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.

More information on Pivot.

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:

Image 1

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:

SQL
DECLARE @cols NVARCHAR (MAX)
SELECT @cols = COALESCE (@cols + ',[' + colName + ']', '[' + colName + ']')
FROM    Table1
WHERE	Conditions
ORDER BY colName

PRINT @cols

The above script gives you the list of columns in string format with commas. Example: [Jan], [Feb], [Mar], [Apr], [May], [Jun], [Jul], [Aug], [Sept], [Oct], [Nov], [Dec]. If you pass this dynamic query to your pivot, then your pivot columns will display dynamically.
The below SQL script creates the stored procedure which returns pivot table as output.

SQL
CREATE PROCEDURE pivot_TeamVsMatches
AS

/*First get the dynamic columns query*/

DECLARE @columnHeaders NVARCHAR (MAX)
SELECT @columnHeaders  = _
	COALESCE (@columnHeaders  + ',[' + month + ']', '[' + month + ']')
FROM    tbl_Matches
ORDER BY month

DECLARE @FinalQuery NVARCHAR(MAX)

SET @FinalQuery = 	‘SELECT *
			FROM
				(SELECT Team,
					   Month
				FROM tbl_Matches
				) A
			PIVOT
				(
				 COUNT(*)
				 FOR ColName
				 IN (‘+@columnHeaders +’)
				) B
ORDER BY Team’
PRINT ‘Pivot Queuery :’+ @FinalQuery
EXECUTE (@FinalQuery)
GO

This stored procedure returns pivot table as below:

Image 2

Now to get the grand total and row, we need to form the COALESCE query. As shown below…

SQL
/* GRAND TOTAL COLUMN */
DECLARE @GrandTotalCol	NVARCHAR (MAX)
SELECT @GrandTotalCol = COALESCE (@GrandTotalCol + ‘ISNULL _
([' + CAST (Month AS VARCHAR) +'],0) + ', 'ISNULL([' + CAST(Month AS VARCHAR)+ '],0) + ')
FROM	tbl_Matches
GROUP BY Month
ORDER BY  Month
SET @GrandTotalCol = LEFT (@GrandTotalCol, LEN (@GrandTotalCol)-1)

The above query returns as below…

SQL
@GrandTotalCol = ISNULL ([' + CAST (Jan AS VARCHAR) +'],0) + ISNULL _
([' + CAST (Feb AS VARCHAR) +'],0) + ISNULL ([' + CAST (March AS VARCHAR) +'],0) + _
…………. + ISNULL ([' + CAST (Dec AS VARCHAR) +'],0).

/* GRAND TOTAL ROW */
DECLARE @GrandTotalRow	NVARCHAR(MAX)
SELECT @GrandTotalRow = COALESCE(@GrandTotalRow + ',ISNULL(SUM([' + _
CAST(Month AS VARCHAR)+']),0)', 'ISNULL(SUM([' + CAST(Month AS VARCHAR)+']),0)')
FROM	tbl_Matches
GROUP BY Month
ORDER BY  Month

The above query returns as below…

SQL
@GrandTotalRow = ISNULL(SUM([' + CAST(Jan AS VARCHAR)+']),0) + _
ISNULL(SUM([' + CAST(Feb AS VARCHAR)+']),0) + ……… + _
ISNULL(SUM([' + CAST(Dec AS VARCHAR)+']),0).

The above COALESCE strings need to be used in dynamic pivot query…

Below is the stored procedure which will give the total output of our requirement.

SQL
CREATE PROCEDURE pivot_TeamVsMatches
AS
/* COLUMNS HEADERS */
DECLARE @columnHeaders NVARCHAR (MAX)
SELECT @columnHeaders  = COALESCE (@columnHeaders  _
+ ',[' + month + ']', '[' + month + ']')
FROM    tbl_Matches
GROUP BY month
ORDER BY month

/* GRAND TOTAL COLUMN */
DECLARE @GrandTotalCol	NVARCHAR (MAX)
SELECT @GrandTotalCol = COALESCE (@GrandTotalCol + ‘ISNULL ([' + _
CAST (Month AS VARCHAR) +'],0) + ', 'ISNULL([' + CAST(Month AS VARCHAR)+ '],0) + ')
FROM	tbl_Matches
GROUP BY Month
ORDER BY Month
SET @GrandTotalCol = LEFT (@GrandTotalCol, LEN (@GrandTotalCol)-1)

/* GRAND TOTAL ROW */
DECLARE @GrandTotalRow	NVARCHAR(MAX)
SELECT @GrandTotalRow = COALESCE(@GrandTotalRow + ',ISNULL(SUM([' + _
CAST(Month AS VARCHAR)+']),0)', 'ISNULL(SUM([' + CAST(Month AS VARCHAR)+']),0)')
FROM	tbl_Matches
GROUP BY Month
ORDER BY  Month

/* MAIN QUERY */
DECLARE @FinalQuery NVARCHAR (MAX)
SET @FinalQuery = 	‘SELECT *, ('+ @GrandTotalCol + ') _
AS [Grand Total] INTO #temp_MatchesTotal
			FROM
				(SELECT Team,
					   Month
				FROM tbl_Matches
				) A
			PIVOT
				(
				 COUNT (*)
				 FOR ColName
				 IN (‘+@columnHeaders +’)
				) B
ORDER BY Team
SELECT * FROM #temp_MatchesTotal UNION ALL
SELECT ''Grand Total'','''','+@GrandTotalRow +', _
ISNULL (SUM([Grand Total]),0) FROM #temp_MatchesTotal
DROP TABLE #temp_MatchesTotal'
-- PRINT 'Pivot Query '+@FinalQuery
EXECUTE(@PivotQuery)
GO

Result as below…

Image 3

Here in this stored procedure, I have used a 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.

Thanks again. Bye.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) Firstsource Sol Ltd.,
India India
Senior Software Engineer with over 10 years of experience in developing and implementing Internet / Intranet applications using Microsoft .NET technologies (ASP.NET,MVC, C#, MSSQL) and have been involved in the entire software development life cycle for several projects. A quick learner, likes to build creative solutions. Ready to work in a team and individually. Communicative, stress resistant.

Comments and Discussions

 
PraiseGratitude Pin
Member 1484731621-Nov-20 3:25
Member 1484731621-Nov-20 3:25 
QuestionDynamic SQL Pivot Query Pin
Member 1469511419-Dec-19 9:01
Member 1469511419-Dec-19 9:01 
Answerits worked for me just copy past my code on sqlserver (i fixed some problems like not have group by and underline..) Pin
snicee9-Feb-17 19:42
snicee9-Feb-17 19:42 
GeneralRe: its worked for me just copy past my code on sqlserver (i fixed some problems like not have group by and underline..) Pin
Narapareddy Shyamprasad1-Apr-18 21:52
Narapareddy Shyamprasad1-Apr-18 21:52 
GeneralRe: its worked for me just copy past my code on sqlserver (i fixed some problems like not have group by and underline..) Pin
Santosh kumar Pithani12-Sep-18 18:34
professionalSantosh kumar Pithani12-Sep-18 18:34 
GeneralRe: its worked for me just copy past my code on sqlserver (i fixed some problems like not have group by and underline..) Pin
Narapareddy Shyamprasad13-Sep-18 19:45
Narapareddy Shyamprasad13-Sep-18 19:45 
Hi Santosh Kumar,
Please let me know how i can help you.

Thanks, Shyam Smile | :)
GeneralRe: its worked for me just copy past my code on sqlserver (i fixed some problems like not have group by and underline..) Pin
Santosh kumar Pithani16-Sep-18 20:25
professionalSantosh kumar Pithani16-Sep-18 20:25 
GeneralRe: its worked for me just copy past my code on sqlserver (i fixed some problems like not have group by and underline..) Pin
Narapareddy Shyamprasad16-Sep-18 20:41
Narapareddy Shyamprasad16-Sep-18 20:41 
GeneralRe: its worked for me just copy past my code on sqlserver (i fixed some problems like not have group by and underline..) Pin
Santosh kumar Pithani16-Sep-18 22:37
professionalSantosh kumar Pithani16-Sep-18 22:37 
Suggestionforgot GROUP BY Pin
snicee9-Feb-17 19:40
snicee9-Feb-17 19:40 
QuestionNeed Sum of Row and Column Pin
arpit140525-Aug-15 20:58
arpit140525-Aug-15 20:58 
QuestionSum for Rows and Column Pin
Member 444291618-Jul-15 1:57
Member 444291618-Jul-15 1:57 
QuestionHelp Pin
Narendra Singh Chauhan13-Jul-15 22:24
Narendra Singh Chauhan13-Jul-15 22:24 
Questiontemp table Pin
Member 1136031624-Apr-15 1:05
professionalMember 1136031624-Apr-15 1:05 
QuestionSample DB and SP Pin
jithesh a25-Sep-14 18:49
jithesh a25-Sep-14 18:49 
Generalnice work my +5v Pin
george498626-Jun-14 19:12
professionalgeorge498626-Jun-14 19:12 
GeneralRe: nice work my +5v Pin
mannu baroli20-Jul-14 22:25
professionalmannu baroli20-Jul-14 22:25 
GeneralRe: nice work my +5v Pin
george49866-Aug-14 18:31
professionalgeorge49866-Aug-14 18:31 
QuestionCan i get sample code and table structure Pin
vishal_h26-Apr-14 5:14
vishal_h26-Apr-14 5:14 
QuestionPlease help me. Pin
testowy4325-Oct-12 7:23
testowy4325-Oct-12 7:23 
QuestionQuery Hang Pin
Member 943933218-Sep-12 6:11
Member 943933218-Sep-12 6:11 
Questionerror in sql server 2005 Pin
winchnet200516-Sep-12 23:59
winchnet200516-Sep-12 23:59 
QuestionCan some one Please help... What seems to be wrong with my cross tab query??? Pin
Jatelo_MVP25-Jun-12 21:56
Jatelo_MVP25-Jun-12 21:56 
GeneralMy vote of 5 Pin
Shahan Ayyub6-May-12 11:28
Shahan Ayyub6-May-12 11:28 
GeneralMy vote of 3 Pin
Chamila Nishantha2-May-12 21:30
Chamila Nishantha2-May-12 21:30 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.