Script for migrating server role permissions - SQL Server
In this case the issue was not with the join but with the text concatenation.

The following should work

SELECT ' GRANT ' + SPerm.permission_name COLLATE Latin1_General_CI_AS + ' TO '  + SP.name -- + ' '' ' AS Commands_To_Execute 
FROM sys.server_principals SP 
  JOIN sys.server_permissions SPerm 
    ON SP.principal_id = SPerm.grantee_principal_id 
    where sp.type='R' and sp.name!='public'


As an aside, I would strongly recommend looking at the Copy-DBAServerRole Powershell cmdlet in DBATools which is a Powershell module - it does the heavy lifting for you in jobs like these.