SQL Server How do I add a Grand total column at the end of a pivot query that is using a dynamic set of column headers?

nbysray5 于 2023-04-04 发布在其他

关注(0)|答案(1)|浏览(134)

I am creating a report for a client and which requires me to produce a Pivot query that uses a dynamic set of columns headers. I have achieved this, however I am struggling to introduce a working Grand total at the end. I know how to do this if the values are fixed but not using a dynamic set of column headers. Any help would be greatly appreciated :)

-- Construct the dynamic PIVOT query
DECLARE @cols AS NVARCHAR(MAX)

-- Get the distinct rates
SELECT @cols = COALESCE(@cols + ', ', '') + QUOTENAME(rate)
FROM (SELECT DISTINCT rate FROM #PayDetailNAme pay JOIN #DutyInfo2 duty ON pay.DUTY_ID = duty.Dutyid ) AS rate;

DECLARE @query AS NVARCHAR(MAX)

-- Construct the dynamic PIVOT query
SET @query = '
SELECT

[start],
[end],
EMPLOYEE,
pin,
' + @cols + '
FROM 
(
SELECT 
''' + @start + ''' as [Start],
''' + @end + ''' as [End],
EMPLOYEE,
pin, 
Rate,
isnull(hours,0) as hours

from #PayDetailNAme pay 
    JOIN #DutyInfo2 duty 
        ON pay.DUTY_ID = duty.Dutyid
) 
AS SourceTable
PIVOT
(
  SUM(hours)
  FOR rate IN (' + @cols + ')
) 
AS PivotTable
WHERE COALESCE(' + @cols + ') IS NOT NULL

;';

I have looked at some online examples but could not get this to behave as expected

sql-server

来源：https://stackoverflow.com/questions/75887233/how-do-i-add-a-grand-total-column-at-the-end-of-a-pivot-query-that-is-using-a-dy

1条答案

按热度按时间

ffdz8vbo1#

As I mentioned in the comments, this is much easier with conditional aggregation rather than the restrictive PIVOT operator. For conditional aggregation, when you want a grand total column you just use SUM without a condition:

SUM(CASE SomeColumn WHEN 'SomeValue' THEN SomeNumeric END) AS SomeValue,
SUM(SomeNumeric) AS SomeTotal

For a dynamic statement, that isn't any different, you just put the SUM after your dynamic conditional aggregates. Assuming SQL Server 2017+ (and you have STRING_AGG as a result), and with made up sample data (as there is none in the question), this results in a query like so:

CREATE TABLE dbo.YourTable (GroupingID int,
                            SomeValue varchar(10),
                            SomeInt int);
GO

INSERT INTO dbo.YourTable (GroupingID,
                           SomeValue,
                           SomeInt)
VALUES(1,'abc',10),
      (1,'def',12),
      (1,'abc',15),
      (2,'abc',9),
      (3,'xyz',12),
      (3,'def',7),
      (4,'xyz',12);
GO

DECLARE @ID int = 3;

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

DECLARE @Delimiter nchar(3) = N',' + @CRLF;

SELECT @SQL = N'SELECT GroupingID,' + @CRLF +
              STRING_AGG(N'       SUM(CASE SomeValue WHEN ' + QUOTENAME(SomeValue,'''') + N' THEN SomeInt END) AS ' + QUOTENAME(SomeValue),@Delimiter) WITHIN GROUP (ORDER BY SomeValue) + N',' + @CRLF +
              N'       SUM(SomeInt) AS GrandTotal' + @CRLF +
              N'FROM dbo.YourTable' + @CRLF +
              N'WHERE GroupingID <= @ID' + @CRLF + --Demonstrate parametrisation
              N'GROUP BY GroupingID;'
FROM (SELECT DISTINCT SomeValue
      FROM dbo.YourTable) YT;
--PRINT @SQL; --Your best friend

EXEC sys.sp_executesql @SQL, N'@ID int', @ID;

GO
DROP TABLE dbo.YourTable;

赞(0）回复(0）举报 2023-04-04

我来回答

SQL Server How do I add a Grand total column at the end of a pivot query that is using a dynamic set of column headers?

1条答案

相关问题

热门标签

最新问答