SQL Dynamic Pivot table with ROW and Column Total

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

I have the above data in a SQL table. I'm able to create the below table via Dynamic Pivot query without the Total columns. Total column is required before the pivot columns and a grand total row at the bottom.

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

Similar questions have been asked in the past, but I have failed to reconstruct for my requirement. Here's my code:

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)

Solution

Assumption :

fy_week is a string data type

to obtain the columnwise total, add to your query 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

and the @cols will need to appended with column name Total. Add below to after your set @cols query

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

for the line level query, you will need another query which is group by fy_week, for this, i have make use of CTE as you need to reference the above x query twice

the complete query. (i reformatted it a bit for my eyes)

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)

EDIT : change to handle fy_week is an integer column