Display Totals at the End

  • Hi

    How to display Totals in the end

    declare @cols as nvarchar(max)='';
    declare @query as nvarchar(max)='';
    select @cols = @cols + QUOTENAME(ItemName) + ',' from (Select distinct ItemName from #temp) as tmp
    select @cols = substring(@cols,0,len(@cols)) set @query = 'select Name, ' + @cols + '
    from (select name,quantity,itemname from #temp) x pivot (Sum(quantity) for itemname in (' + @cols + ')) piv ';
    execute (@query)


  • Honestly, I would create your entire statement on the fly and use conditional aggregation, not the restrictive PIVOT operator, then you'll find this much easier. Also the syntax SELECT @Variable = @Variable + ... FROM is a documented antipattern, so you should get away from using that. As you're on SQL Server 2014, you'll need to use the "old" FOR XML PATH method, rather than STRING_AGG.

    This should get you most of the way there if not all of it; you'll need to use your "best friend" for debugging, as I can't run the statement as there's no sample data in your post for me to test:

    DECLARE @SQL nvarchar(MAX),
    @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

    SET @SQL = N'SELECT Name,' + @CRLF +
    STUFF((SELECT N',' + @CRLF +
    N' SUM(CASE ItemName WHEN N' + QUOTENAME(YT.ItemName, '''') + N' THEN quantity END) ' + QUOTENAME(YT.ItemName)
    FROM dbo.YourTable YT
    GROUP BY YT.ItemName
    ORDER BY YT.ItemName
    FOR XML PATH(N''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,3,N'') + N',' + @CRLF +
    N' SUM(quantity) AS TotalQuantity' + @CRLF +
    N'FROM dbo.YourTable' + @CRLF +
    N'GROUP BY Name;';

    --PRINT @SQL; --Your best friend
    EXEC sys.sp_executesql @SQL;

    • This reply was modified 2 months ago by  Thom A.


    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply