It might help to change how you approach the problem from the very beginning. There are a lot of sub-queries, all hitting the same remote table, and all with mostly the same WHERE condition. Rather than thinking about the end result as a collection of separate pieces, think about it in terms of one big set of data, and that you want to tally up how many rows meet certain criteria as you move through each row once.

Assuming that the code in the Question wasn't overly over-simplified, you should be able to do the following, which uses a Table Variable to hold multiple values of "Code" per the given "Key". Once we have those captured, then we can simply JOIN that to the remote table. Also, I got rid of the DISTINCT on the @Sys lookup as it was only there due to the SELECT being in a subquery which isn't necessary.

DECLARE @Codes TABLE (Code VARCHAR(5) NOT NULL);
DECLARE @Sys INT
DECLARE @Key INT

SET @Key = 12345

SELECT @Code = Code
FROM LOOKUP.Table1
WHERE [Key] = @Key;

SELECT @Sys = [System] -- multiple rows may return,
FROM LOOKUP.Table1     -- but they all have the same value
WHERE [Key] = @Key;


SELECT cd.Code,
       SUM(IIF(th.[Type] NOT LIKE 'X%', 1, 0)) AS [Total],
       SUM(IIF(th.[Type] LIKE 'Y%', 1, 0)) AS [TypeA],
       SUM(IIF(th.[Type] LIKE 'N%', 1, 0)) AS [TypeB]
FROM   @Codes cd
INNER JOIN [LINKED-SERVER].[DB].[SCHEMA].[Table_H] th
        ON th.[Key] = @Key
       AND th.[Code] = cd.[Code]
GROUP BY th.[Code];

It might help to change how you approach the problem from the very beginning. There are a lot of sub-queries, all hitting the same remote table, and all with mostly the same WHERE condition. Rather than thinking about the end result as a collection of separate pieces, think about it in terms of one big set of data, and that you want to tally up how many rows meet certain criteria as you move through each row once.

Assuming that the code in the Question wasn't overly over-simplified, you should be able to do the following, which uses a Table Variable to hold multiple values of "Code" per the given "Key". Once we have those captured, then we can simply JOIN that to the remote table. We JOIN on the criteria that is common to all of the subqueries, and then place the unique criteria within the IIF().

Also, I got rid of the DISTINCT on the @Sys lookup as it was only there due to the SELECT being in a subquery which isn't necessary. And while you could use TOP 1 rather than DISTINCT, there is no reason to force a sort given that it will be the same value that comes back each time, hence the process won't notice that @Sys is potentially being overwritten up to 5 times (with the exact same value). Of course, the @Sys variable is not being used in the original query, so maybe it can be removed entirely?

DECLARE @Codes TABLE (Code VARCHAR(5) NOT NULL);
DECLARE @Sys INT
DECLARE @Key INT

SET @Key = 12345

INSERT INTO @Codes (Code)
  SELECT Code
  FROM   LOOKUP.Table1
  WHERE  [Key] = @Key; 

-- @Sys is not used in the final query. Should this be removed?
SELECT @Sys = [System] -- multiple rows may return,
FROM LOOKUP.Table1     -- but they all have the same value
WHERE [Key] = @Key;


SELECT cd.Code,
       SUM(IIF(th.[Type] NOT LIKE 'X%', 1, 0)) AS [Total],
       SUM(IIF(th.[Type] LIKE 'Y%', 1, 0)) AS [TypeA],
       SUM(IIF(th.[Type] LIKE 'N%', 1, 0)) AS [TypeB]
FROM   @Codes cd
INNER JOIN [LINKED-SERVER].[DB].[SCHEMA].[Table_H] th
        ON th.[Key] = @Key
       AND th.[Code] = cd.[Code]
GROUP BY th.[Code];