Add total column at the end, for a dynamic pivot SQL Server query

I could manage to make a dynamic pivot query in SQL Server as below:

DECLARE @sql nvarchar(max),
        @columns nvarchar(max) 

SELECT @columns = STUFF((SELECT DISTINCT ',' + QUOTENAME(nationality) 
                         FROM NewComers  
                         FOR XML PATH('')), 1, 1, '')

SELECT @sql = 'SELECT p.registrationdate, '+ @columns +
              ' FROM (SELECT s.registrationdate, s.Nationality FROM newcomers s) AS t
                PIVOT (
                    COUNT(Nationality) FOR nationality IN (' + @columns+ ')) p'

EXEC (@sql)

My problem is that I want to add a column at the end of the result. So currently I get this result:

  registrationdate   GER   TUR   CAN   AUS  
 ------------------ ----- ----- ----- ----- 
  16/11/2016          10     8     6     7  
  21/08/2020           4     5     3     2  
  08/04/2019           1     3     5     0  

and I need to have this as result:

  registrationdate   GER   TUR   CAN   AUS   Total  
 ------------------ ----- ----- ----- ----- ------- 
  16/11/2016          10     8     6     7      31  
  21/08/2020           4     5     3     2      14  
  08/04/2019           1     3     5     0       9  

I tried several solutions in Stack Overflow but I nothing quite worked.

Will appreciate your help :)

Can you add the Total as unioned rows with your newcomer table and then manually add the “Total” string to your @columns variable?

Basically create a CTE of newcomer table with a union which groups by your date and sums the total up and stick the word “Total” into the nationality column.

Then just add the Total into your @columns string to pivot it out.

Something like this? I don't have access to a SQL Server currently to test it.

DECLARE @sql nvarchar(max),
        @columns nvarchar(max) 

SELECT @columns = STUFF((SELECT DISTINCT ',' + QUOTENAME(nationality) 
                         FROM NewComers  
                         FOR XML PATH('')), 1, 1, '')

SELECT @sql = '

;with CTE as (
 select registrationdate, Nationality, count(Nationality) as Cnt from newcomers group by registrationdate, Nationality
 union all
 select registrationdate, ''Total'', count(Nationality) as Cnt from newcomers group by registrationdate 
)

SELECT p.registrationdate, '+ @columns + ', Total' +
              ' FROM (SELECT registrationdate, Nationality, Cnt FROM CTE) AS t
                PIVOT (
                    max(Cnt) FOR nationality IN (' + @columns+ ',Total)) p'

EXEC (@sql)