Column total at the end of SQL pivot query

Question

5.00/5 (1 vote)

See more:

I have generated the following SQL Server pivot, and it gives me desire result. I want to add total column at end of pivot, where I am finding it difficult.

Please find the SQL I'm using for pivot,

SQL

DECLARE @columns NVARCHAR(MAX)
DECLARE @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT   @columns =  @columns+N', p.' +QUOTENAME(Name)
  FROM (
		SELECT distinct Name  FROM #Temp2

   ) AS x;
SET @sql = N'
SELECT 
        P.PNAME,
	
SUM(' + @columns + ') AS TOTAL,  -- here am getting error like incorrect syntaxt near ','
 ' + STUFF(@columns, 1, 2, '') + '
FROM
(
             SELECT P.ProductCode,	
				
				
	 CONVERT(VARCHAR,YEAR(PT.BILLDATE))+''_''+DATENAME(MM,PT.BILLDATE) AS MonthNames  
              FROM TEST PT (NOLOCK)
                    INNER JOIN  Products P (NOLOCK) ON PT.PNAME=P.PNAME

  
) AS j
PIVOT
(
  SUM(SaleQuantity) FOR MonthNames IN ('
  + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
  + ')
) AS p;';

PRINT @sql;
EXEC sp_executesql @sql;

DROP TABLE #Temp2

thanks in advance

What I have tried:

Column Total at the end of SQL Pivot query

Posted 2-May-16 21:39pm

stellus

Updated 3-May-16 3:25am

CHill60

v2

Add a Solution

Comments

CHill60 3-May-16 3:48am

Can you share some sample data for both tables or perhaps create a SQL Fiddle[^]

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Answer 1 · 2016-05-02T22:30:00

Here is an example using the Microsoft Northwind database. I created a simple pivot on the Orders table as follows

SQL

SELECT CustomerID,	ROW_NUMBER() OVER (ORDER BY CustomerID) as rn,
			ISNULL([1996],0) as [1996],
			ISNULL([1997],0) as [1997],
			ISNULL([1998],0) as [1998]
INTO #temp
FROM 
(
	SELECT CustomerID, YEAR(OrderDate) as Y, Freight
	FROM ORDERS
	WHERE CustomerID IN ('ALFKI','BLAUS','DRACD')
) source
PIVOT
(
	SUM(Freight) FOR Y IN ([1996],[1997],[1998])
) pvt

Points to note - I've inserted the PIVOT results into a temporary table (you can just as easily use a Table variable).
I've also included a row number.

The results of that are:

ALFKI	1	0.00	114.42	111.16
BLAUS	2	0.00	38.64	129.62
DRACD	3	35.99	33.35	236.70

I can then union that with a total, using the row number to make sure the Total ends up at the bottom.

SQL

select CustomerID, [1996],[1997],[1998] from 
(
	select rn, CustomerID, [1996],[1997],[1998] FROM #temp
	UNION
	select MAX(rn) + 1, 'TOTAL', SUM([1996]),SUM([1997]),SUM([1998])
	from #temp
) source
ORDER BY rn

I use a sub-query here because I don't want the row number to be part of the final query, but it's not essential if you just ignore the row number in your presentation layer.

Results after the final UNION :

ALFKI	0.00	114.42	111.16
BLAUS	0.00	38.64	129.62
DRACD	35.99	33.35	236.70
TOTAL	35.99	186.41	477.48

Herman<T>.Instance · Answer 2 · 2016-05-03T03:25:00

Solution 2

See my example on CP called Dynamic Pivoting with Cubes and eventhandlers in SQL Server 2005, 2008 and 2008 R2[^].

Posted 3-May-16 3:25am

Herman<T>.Instance