none
Help! Need grand total at bottom of this SQL pivot table.

    Question

  • Anyone able to add to my code to get me grand totals at the bottom of the report?

    My code =

    DECLARE @sql NVARCHAR(MAX)
    DECLARE @pivotsql VARCHAR(MAX)
    DECLARE @columns VARCHAR(MAX)
    DECLARE @selectlist VARCHAR(MAX)
    
    SET @columns = N'';
    SELECT @columns += N', c.' + QUOTENAME(urn)
    FROM (SELECT c.urn FROM Cashbook.MappingMenu AS c
    LEFT JOIN Cashbook.CashDetail AS m ON m.MappingMenu = c.urn
    GROUP BY c.urn, m.MappingMenu) AS x;
    
    SET @selectlist = N'';
    SELECT @selectlist += N', ISNULL(' + QUOTENAME(urn) + ', 0) AS ' +
    QUOTENAME(urn) FROM (SELECT c.urn FROM Cashbook.MappingMenu AS c
    LEFT JOIN Cashbook.CashDetail AS m ON m.MappingMenu = c.urn
    GROUP BY c.urn, m.MappingMenu) AS x;
    
    SET @selectlist = STUFF(@selectlist, 1, 1, '');
    SET @pivotsql = N'SELECT CONVERT (VARCHAR, CreatedOn, 103) AS Date,  ' + @selectlist + '
    FROM (SELECT CreatedOn, MappingMenu, Value FROM Cashbook.CashDetail AS m) AS j
    
    PIVOT
    
    (SUM(Value) FOR MappingMenu IN ('+ STUFF(REPLACE(@columns, ', c.[', ',['), 1, 1, '') + ')) AS c';
    
    EXEC (@pivotsql)

    Thanks in advance!

    BUMP: anyone?


    Saturday, November 09, 2013 6:05 PM

Answers

  • Hi Stacey,

    What’s the error you encounter with the codes? I suggest printing the commands out before executing it, checking them and make sure there is no syntax error and the final codes are what we want.

    Best Regards,
    Allen Li


    Allen Li
    TechNet Community Support

    Tuesday, November 12, 2013 3:05 AM

All replies

  • SET @pivotsql = N'SELECT CONVERT (VARCHAR, CreatedOn, 103) AS Date,  ' + @selectlist + '
    FROM (SELECT CreatedOn, MappingMenu, Value FROM Cashbook.CashDetail AS m) AS j

    PIVOT

    (SUM(Value) FOR MappingMenu IN ('+ STUFF(REPLACE(@columns, ', c.[', ',['), 1, 1, '') + ')) AS c';

    Hi, Try inserting the result set of the above query to a temporary table. Finally we can query the temporary table to get the grand total.  Or you can use the subquery technique.


    Regards, RSingh


    • Edited by RSingh() Monday, November 11, 2013 6:17 AM
    Monday, November 11, 2013 4:24 AM
  • I tried running your code  but keep getting 

    Msg 208, Level 16, State 1, Line 7
    Invalid object name 'Cashbook.MappingMenu'.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, November 11, 2013 6:50 AM
  • So this question was asked in precisely this forum just a few days ago, and there were some really good answers to that thread. So just go backwards in the thread display.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, November 11, 2013 2:10 PM
  • Hi Stacey,

    What’s the error you encounter with the codes? I suggest printing the commands out before executing it, checking them and make sure there is no syntax error and the final codes are what we want.

    Best Regards,
    Allen Li


    Allen Li
    TechNet Community Support

    Tuesday, November 12, 2013 3:05 AM