Total (SUM) Row whilst using dynamic Pivot

Question

I am trying to Total (SUM) up each row whilst using a dynamic pivot. As shown in the image.

I have managed to get the SUM for each field (site), what I now need is a column that Totals (SUMS) each of row (which can contain multiple Sites). (If its possible by extending my current tsql)

Here is my working pivot. drawpersite is a view that contains the fields

Name(varchar(50)),Site(varchar(50)), Draw(decimal(19, 4),Trade(varchar(50))

I have put this into a view to hide all of the joins I require to get the data. The below query is mostly cobbled together, but working as expected (Apart from the total of all sites (Total of the row)

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT
  @columns += N', p.' + QUOTENAME([SiteName])
FROM (SELECT
    [Site] AS [SiteName]
  FROM [dbo].[drawpersite] AS p
  GROUP BY [Site]) AS x;
SET @sql = N'
SELECT [Name],[Trade], ' + STUFF(@columns, 1, 2, '') + ' FROM (
SELECT [Name],[Trade], [Draw] AS [Quantity], [Site] as [SiteName] 
    FROM [dbo].[drawpersite]) AS j PIVOT (SUM(Quantity) FOR [SiteName] in 
       (' + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '') + ')) AS p;';
EXEC sp_executesql @sql

I have seen other questions on this, but I am still struggling to work it out.

EDIT

Here this the data from the view DrawPerSite, this is a flat list with no grouping. It will show each Draw per site as a new record, like a log. So each user(Name) can have multiple entries, even multiple per site.

I am taking this and need to show per Name, eg Admin the total Draw(Amount) for each site.

So from the data above, with my current pivot query I get this result set. This is the result set I need the total per row.

EDIT2

Screen shot to show second record not totaling.

Answer 1

I would just add the sites together at the next level up:

DECLARE
    @columns NVARCHAR(MAX)
    , @columns_outer NVARCHAR(MAX)
    , @sums NVARCHAR(MAX)
    , @sql NVARCHAR(MAX);

SET @columns = N'';
SET @columns_outer = N'';
SET @sums = N'';

SELECT @columns += N', p.' + QUOTENAME([SiteName])
FROM
    (
        SELECT [Site] AS [SiteName]
        FROM [dbo].[drawpersite] AS p
        GROUP BY [Site]
    ) AS x;

SELECT @columns_outer += N', p2.' + QUOTENAME([SiteName])
FROM
    (
        SELECT [Site] AS [SiteName]
        FROM [dbo].[drawpersite] AS p
        GROUP BY [Site]
    ) AS x;

SELECT @sums += N' + ISNULL(' + QUOTENAME([SiteName]) + ', 0)'
FROM
    (
        SELECT [Site] AS [SiteName]
        FROM [dbo].[drawpersite] AS p
        GROUP BY [Site]
    ) AS x;

SET @sql = N'
    SELECT [Name], [Trade], ' + STUFF(@columns_outer, 1, 2, '') + ', ' + STUFF(@sums, 1, 3, '') + ' [Total] FROM (
    SELECT [Name], [Trade], ' + STUFF(@columns, 1, 2, '') + ' FROM (
    SELECT [Name],[Trade], [Draw] AS [Quantity], [Site] as [SiteName] 
    FROM [dbo].[drawpersite]) AS j PIVOT (SUM(Quantity) FOR [SiteName] in 
    (' + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '') + ')) AS p) AS p2;';

EXEC sp_executesql @sql