Pivot Tables in SQL

Transact-SQL
1

Hi all

I’m new to Pivot tables in SQL, below is my query but I’m battling to get the Grand totals at the end of each line, hope someaone can help me

Thanks

DECLARE @Cols VARCHAR(MAX);
DECLARE @SQL NVARCHAR(MAX);

         SELECT @Cols = COALESCE (@Cols + ',[' + convert(varchar,MS.WeekID) + ']', '[' + convert(varchar,MS.WeekID) + ']') 
                      FROM GH_MarketStats MS
                           inner join GH_ExportSeasons ES on ES.ID=MS.SeasonID
                           inner join GH_ExportMarkets EM on EM.ID=MS.MarketID
           where  ES.ProductGroupID = @ProductGroupID
           GROUP BY MS.WeekID
           ORDER  BY MS.WeekID;

SELECT @SQL = CONCAT('SELECT * FROM (',
        'SELECT EM.[Name], MS.WeekID, (MS.WeightbyAir + MS.WeightbySea) as Tot',
        ' FROM GH_MarketStats MS',
        ' INNER JOIN GH_ExportSeasons ES on ES.ID = MS.SeasonID',
        ' INNER JOIN GH_ExportMarkets EM on EM.ID = MS.MarketID',
        ' WHERE ES.ProductGroupID = ''', @ProductGroupID, '''',
        ' AND MS.SeasonID = ', @SeasonID,
		' AND MS.CropID = ''', @CropID, '''',
  ') AS SourceTable',
  ' PIVOT (',
        ' SUM(Tot)',
        ' FOR WeekID IN (',@cols, ')',
 ' ) AS PivotTable'
);

   EXEC SP_EXECUTESQL @SQL

END

Currently this is what I have showing using above query :

Name 22 23 24 25 26

Canada NULL NULL NULL NULL 2607
China NULL NULL 359 NULL NULL
Europe NULL NULL NULL 2100 NULL
Hong Kong NULL 1250 NULL NULL 1152
India NULL NULL NULL NULL NULL
Taiwan 8472 6585 NULL NULL NULL
Thailand NULL NULL 5250 4989 NULL