Click here to Skip to main content
15,031,941 members
Please Sign up or sign in to vote.
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
Updated 3-May-16 3:25am
v2
Comments
CHill60 3-May-16 3:48am
   
Can you share some sample data for both tables or perhaps create a SQL Fiddle[^]

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
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900