Добавить столбец Total в сводную таблицу Dynamic SQL

Много таких вопросов, но не могу найти ничего, что соответствует тому, что я ищу.

Я использую код из другого поста, который отлично работает, но я хотел бы изменить его, чтобы добавить строку итогов внизу таблицы.

Предыдущий пост, чтобы создатель получил признание: Динамический свод SQL Server с несколькими столбцами< /а>

Моя текущая таблица выглядит так:

Work_Group Avg 01/01/2021 01/01/2021 01/01/2021 ect...
Blue 5 2 5 8
Green 5 2 5 8

Цель:

Work_Group Avg 01/01/2021 01/01/2021 01/01/2021 ect...
Blue 5 2 5 8
Green 5 2 5 8
TOTAL 10 4 10 16

Текущий код:

DECLARE @colsEAST AS NVARCHAR(MAX);
DECLARE @pivotCountEAST AS NVARCHAR(MAX);
DECLARE @sqlEAST AS NVARCHAR(MAX);

SELECT @colsEAST = ISNULL(@colsEAST + ', ', '') + QUOTENAME(Load_Date)
FROM (SELECT DISTINCT Load_Date FROM ##CE_tmpEast_ALL) AS Load_Date

SET @pivotCountEAST = N'SELECT Work_Group, ' + @colsEAST +'
FROM (SELECT Work_Group, Load_Date, Count FROM ##CEtmp_East_ALL) AS Count
PIVOT(SUM(Count) FOR Load_Date IN (' + @colsEAST + ')) AS pvt';

SET @sqlEAST = '; WITH MTD_CountPivot AS (
'+@pivotCountEAST+'
),

MTD_Pivot_Tables AS (
  SELECT Work_Group, AVG(Count) AS [AVG Count]
  FROM ##CEtmp_East_ALL
  GROUP BY Work_Group
)
SELECT MTD_CountPivot.Work_Group,  MTD_Pivot_Tables.[AVG Count], ' + @colsEAST + '
FROM
MTD_CountPivot

INNER JOIN MTD_Pivot_Tables ON MTD_CountPivot.Work_Group = MTD_Pivot_Tables.Work_Group';

EXEC sp_executesql @sqlEAST;

Любая помощь будет принята с благодарностью!!!

Спасибо


sql sql-server pivot sql-server-2016 subtotal
person S Stef    schedule 29.01.2021    source источник
comment
добавить объединение всех во входную таблицу? с группой (по фиктивному столбцу) + сумма   -  person AnkurSaxena    schedule 29.01.2021

Ответы (1)


arrow_upward
2
arrow_downward

Вы можете добавить GROUPING SETS ((mcp.Work_Group),()) к текущему запросу, чтобы получить промежуточные итоги. Для этого необходимо создать еще один параметр @colsEAST2, чтобы хранить сгенерированный список дат для заголовков столбцов в виде

SUM([2001-01-01]) AS [2001-01-01],SUM([2001-01-02]) AS [2001-01-02],SUM([2001-01-03]) AS [2001-01-03]

вместе с @colsEAST, который содержит

[2001-01-01],[2001-01-02],[2001-01-03]

DECLARE @colsEAST       AS NVARCHAR(MAX);
DECLARE @colsEAST2      AS NVARCHAR(MAX);
DECLARE @pivotCountEAST AS NVARCHAR(MAX);
DECLARE @sqlEAST        AS NVARCHAR(MAX);

SELECT @colsEAST = ISNULL(@colsEAST + ', ', '') + QUOTENAME(Load_Date)
  FROM (SELECT DISTINCT Load_Date 
          FROM ##CE_tmpEast_ALL) AS Load_Date;    
SELECT @colsEAST2 = ISNULL(@colsEAST2 + ', ', '') 
                + ' SUM('+QUOTENAME(Load_Date)+') AS '+QUOTENAME(Load_Date)
  FROM (SELECT DISTINCT Load_Date 
          FROM ##CE_tmpEast_ALL) AS Load_Date;    
SET @pivotCountEAST = N'SELECT Work_Group, ' + @colsEAST2 +'
                          FROM (SELECT Work_Group, Load_Date, Count 
                                  FROM ##CE_tmpEast_ALL) AS Count
                         PIVOT(SUM(Count) FOR Load_Date IN (' + @colsEAST + ')) AS pvt
                         GROUP BY Work_Group';                             
SET @sqlEAST = '; WITH MTD_CountPivot AS (
'+@pivotCountEAST+'
), MTD_Pivot_Tables AS (
  SELECT Work_Group, AVG(Count) AS [AVG Count]
    FROM ##CE_tmpEast_ALL
   GROUP BY Work_Group
)
SELECT CASE WHEN GROUPING(mcp.Work_Group) = 0 
            THEN mcp.Work_Group 
            ELSE ''TOTAL'' 
             END AS Work_Group,
       SUM(mpt.[AVG Count]) AS Avg, 
       ' + @colsEAST2+ '
  FROM MTD_CountPivot mcp
  JOIN MTD_Pivot_Tables mpt 
    ON mcp.Work_Group = mpt.Work_Group
 GROUP BY GROUPING SETS ((mcp.Work_Group),())';    
EXEC sp_executesql @sqlEAST;

Демо

person Barbaros Özhan    schedule 29.01.2021
comment
Это прекрасно работает - Спасибо! - person S Stef; 31.01.2021
comment
Не за что @S Stef - person Barbaros Özhan; 31.01.2021