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?

<!-- language: lang-sql -->

    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];