Display Totals at the End

• jsshivalik

  SSChasing Mays

  Points: 612

  More actions

  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

• Thom A

  SSC Guru

  Points: 99130

  More actions

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

  Thom~

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