Динамическая сводная таблица SQL с количеством строк и столбцов

Question

E_ID Full_name      Dept        fy_week fy_rev
45  Tom Smith       Sales       201801  100
65  Mike Tod        Marketing   201801  50
12  Chris Thomson   Sales       201803  60
85  Paul Henry      Sales       201804  40
32  Mich Dowell     Sales       201802  50
65  Mike Tod        Marketing   201803  70
12  Chris Thomson   Sales       201802  80
85  Paul Henry      Sales       201803  90

У меня есть вышеуказанные данные в таблице SQL. Я могу создать таблицу ниже с помощью динамического сводного запроса без столбцов Total. Столбец итога необходим перед столбцами сводки и строкой итогового итога внизу.

E_ID Full_name      Dept    Total   201801  201802  201803  201804
12  Chris Thomson   Sales   $140        $0      $80 $60     $0
32  Mich Dowell     Sales    $50        $0      $50  $0     $0
45  Tom Smith       Sales   $100      $100       $0  $0     $0
65  Mike Tod    Marketing   $120       $50       $0 $70     $0
85  Paul Henry      Sales   $130        $0       $0 $90    $40
Total                       $540      $150     $130 $220       $40

Подобные вопросы задавались в прошлом, но мне не удалось восстановить для моего требования. Вот мой код:

DECLARE 
@cols   AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(fy_week) y
        FROM my_sample_table z
        ORDER BY y asc
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

set @query = 'SELECT e_id, full_name, dept, ' + @cols + ' 
    from ( select e_id, full_name, dept from my_sample_table ) x
       pivot 
        ( Sum(fy_rev) for fy_week in (' + @cols + ')) p '
execute(@query)

Answer 1

Предположение:

• fy_week - строковый тип данных

чтобы получить итоговое значение по столбцам, добавьте к вашему запросу x

from 
( 
    -- your original query
    select E_ID, Full_name, Dept, fy_week, fy_rev -- you missed the fy_week & fy_rev here
    from   my_sample_table 

    -- add the following few lines : union all & select query
    union all

    select E_ID, Full_name, Dept, fy_week = ''Total'', fy_rev = sum(fy_rev)
    from   my_sample_table 
    group by E_ID, Full_name, Dept
) x

и к @cols необходимо добавить имя столбца Total. Добавьте ниже, чтобы после set @cols запрос

select  @cols = '[Total],' + @cols

для запроса на уровне строки вам понадобится еще один запрос, сгруппированный по fy_week, для этого я использовал CTE, так как вам нужно сослаться на приведенное выше. x запрос дважды

полный запрос. (я переформатировал это немного для моих глаз)

DECLARE 
    @cols   AS NVARCHAR(MAX), 
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(fy_week) y
            FROM my_sample_table z
            ORDER BY y asc
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')

SET @cols = '[Total],' + @cols           -- added this line

-- added cte query
SET @query  = '
    ; with cte as
    (
            select  E_ID, Full_name, Dept, 
                    fy_week = convert(varchar(10), fy_week), fy_rev 
            from    my_sample_table 

            union all

            select  E_ID, Full_name, Dept, 
                    fy_week = ''Total'', fy_rev = sum(fy_rev) 
            from    my_sample_table 
            group by E_ID, Full_name, Dept
    )
    SELECT E_ID, Full_name, Dept, ' 
        + @cols + ' 
    from 
    ( 
        select  E_ID, Full_name, Dept, fy_week, fy_rev 
        from    cte

        -- the following is for row wise total
        union all

        select  E_ID = 99, Full_name = ''Total'', Dept = '''', fy_week, sum(fy_rev) 
        from    cte 
        group by fy_week
    ) x
    pivot 
    ( 
        Sum(fy_rev) 
        for fy_week in (' + @cols + ')
    ) p '

-- print out to validate
print   @query
execute(@query)

РЕДАКТИРОВАТЬ: изменить для обработки fy_week является целочисленным столбцом