SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE data(
   Unique_Key INT,
   System_Name varchar(50)
);


	
INSERT INTO data VALUES (1,'IT');
INSERT INTO data VALUES (1,'ACCOUNTS');
INSERT INTO data VALUES (1,'PAYROLL');
INSERT INTO data VALUES (2,'IT');
INSERT INTO data VALUES (2,'PAYROLL');
INSERT INTO data VALUES (3,'IT');
INSERT INTO data VALUES (4,'HR');
INSERT INTO data VALUES (5,'PAYROLL');
8 rows affected

DECLARE @System_Name NVARCHAR(50) = 'IT' 
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @parameter AS NVARCHAR(MAX);
    
SET @parameter = '@System_Name NVARCHAR(50)'

select DISTINCT System_Name
into #staging
from dbo.data t1
WHERE t1.System_Name <> @System_Name



SET @cols = STUFF((SELECT distinct ', SUM(IIF(System_Name = '''+ System_Name+''',1,0))  * 100.0 / SUM(IIF(System_Name = @System_Name,0,1)) ' + QUOTENAME(System_Name) 
            FROM #staging
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
        
set @query = 'SELECT SUM(IIF(System_Name = @System_Name,0,1)) [Distinct Count IT Key], ' + @cols + ' from  dbo.data t1
 WHERE EXISTS (
  SELECT 1 
  FROM dbo.data tt 
  WHERE tt.Unique_Key = t1.Unique_Key
  AND tt.System_Name = @System_Name
 ) '
 
EXECUTE sp_executesql @query, @parameter,  @System_Name


Distinct Count IT Key
ACCOUNTS
HR
PAYROLL


3
33.333333333333
0.000000000000
66.666666666666


DECLARE @System_Name NVARCHAR(50) = 'IT' 
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @parameter AS NVARCHAR(MAX);
    
SET @parameter = '@System_Name NVARCHAR(50)'

select DISTINCT System_Name
into #staging
from dbo.data t1
WHERE t1.System_Name <> @System_Name



SET @cols = STUFF((SELECT distinct ', SUM(IIF(System_Name = '''+ System_Name+''',1,0))  * 100.0 / SUM(IIF(System_Name = @System_Name,0,1)) ' + QUOTENAME(System_Name) 
            FROM #staging
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
        
set @query = 'SELECT SUM(IIF(System_Name = @System_Name,0,1)) [Distinct Count IT Key], ' + @cols + ' from  dbo.data t1
 WHERE EXISTS (
  SELECT 1 
  FROM dbo.data tt 
  WHERE tt.Unique_Key = t1.Unique_Key
  AND tt.System_Name = @System_Name
 ) '
 
EXECUTE sp_executesql @query, @parameter,  @System_Name
Msg 2714 Level 16 State 6 Line 8
There is already an object named '#staging' in the database.