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

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

Herman<T>.Instance · Answer 1 · 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

CHill60 · Answer 2 · 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

Column total at the end of SQL pivot query

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Column total at the end of SQL pivot query

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us