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(20)
);

insert into data values
(1,	'IT'),
(1,	'ACCOUNTS'),
(1,	'PAYROLL'),
(2,	'IT'),
(2,	'PAYROLL'),
(3,	'IT'),
(4,	'HR'),
(5,	'PAYROLL');
8 rows affected

declare @sql nvarchar(max);

; with cte as
(
    select distinct System_Name
    from   data
    where  System_Name <> 'IT'
)
select  @sql = string_agg(sql1 + ' / ' + sql2, ',' + char(13))
from    cte
        cross apply
        (
            select sql1 = char(9) + quotename(System_Name) + ' = '
                        + 'count(case when System_Name = ''' + System_Name + ''' then Unique_Key end) * 100.0 ',
                   sql2 = 'count(distinct Unique_Key)'
        ) a

select  @sql = 'with cte as' + char(13)
             + '(' + char(13)
             + '    select it.Unique_Key, ot.System_Name' + char(13)
             + '    from   data it' + char(13)
             + '           left join data ot on it.Unique_Key = ot.Unique_Key' + char(13)
             + '                            and ot.System_Name <> ''IT''' + char(13)
             + '    where  it.System_Name = ''IT''' + char(13)
             + ')' + char(13)
             + 'select [ITKey] = count(distinct Unique_Key), ' + char(13)
             + @sql + char(13)
             + 'from   cte;' + char(13)

print @sql;

exec sp_executesql @sql;


ITKey
ACCOUNTS
HR
PAYROLL


3
33.333333333333
0.000000000000
66.666666666666


with cte as
(
    select it.Unique_Key, ot.System_Name
    from   data it
           left join data ot on it.Unique_Key = ot.Unique_Key
                            and ot.System_Name <> 'IT'
    where  it.System_Name = 'IT'
)
select [ITKey] = count(distinct Unique_Key), 
	[ACCOUNTS] = count(case when System_Name = 'ACCOUNTS' then Unique_Key end) * 100.0  / count(distinct Unique_Key),
	[HR] = count(case when System_Name = 'HR' then Unique_Key end) * 100.0  / count(distinct Unique_Key),
	[PAYROLL] = count(case when System_Name = 'PAYROLL' then Unique_Key end) * 100.0  / count(distinct Unique_Key)
from   cte;

Warning: Null value is eliminated by an aggregate or other SET operation.


with cte as
(
    select it.Unique_Key, ot.System_Name
    from   data it
           left join data ot on it.Unique_Key = ot.Unique_Key
                            and ot.System_Name <> 'IT'
    where  it.System_Name = 'IT'
)
select  [ITKey]    = count(distinct Unique_Key), 
	[ACCOUNTS] = count(case when System_Name = 'ACCOUNTS' then Unique_Key end) * 100.0 
	           / count(distinct Unique_Key),
	[HR]       = count(case when System_Name = 'HR'       then Unique_Key end) * 100.0 
	           / count(distinct Unique_Key),
	[PAYROLL]  = count(case when System_Name = 'PAYROLL'  then Unique_Key end) * 100.0 
	           / count(distinct Unique_Key)
from   cte;


ITKey
ACCOUNTS
HR
PAYROLL


3
33.333333333333
0.000000000000
66.666666666666


Warning: Null value is eliminated by an aggregate or other SET operation.