Post reply

Display Totals at the End

  • April 26, 2022 at 4:07 am

    #4022222

    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)

    Thanks

  • April 26, 2022 at 10:53 am

    #4022380

    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 years ago by  Thom A.

    Thom~

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

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

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