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,
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