Внутреннее соединение переменное количество раз для расширения дочерних/родительских отношений различной длины

Как отмечали другие в комментариях, вам нужно будет использовать динамический SQL. Это справедливо в любое время, когда количество выходных столбцов зависит от входных данных и не известно заранее.

Общая техника заключается в следующем:

1. Определите повторяющиеся части запроса. В вашем случае вам нужен динамический SQL для вашего списка выбора и для ваших объединений.
2. Создавайте эти повторяющиеся фрагменты SQL с помощью запросов, которые адаптируют шаблонный фрагмент SQL со значениями для каждого вхождения и объединяют эти повторяющиеся фрагменты в одну строку.
3. Объедините эти фрагменты SQL с фиксированным SQL.
4. Выполните объединенный динамический SQL, используя либо EXEC (@Sql), либо EXEC sp_executesql @Sql, '<parameter definitions>', <parameter-list>. (Последнее необходимо, если вам нужно передать один или несколько параметров в динамический SQL или из него.)

Предполагая, что команда высшего уровня всегда имеет нулевой уровень и что в номерах уровней нет пробелов, следующее должно дать вам желаемые результаты.

DECLARE @SelectItems NVARCHAR(MAX) = (
    SELECT STRING_AGG(CA.Item, ',') WITHIN GROUP(ORDER BY Level)
    FROM (SELECT DISTINCT T.Level FROM Teams T) L
    CROSS APPLY (
        SELECT CONCAT(
            CHAR(13), SPACE(8),
            'L', L.Level, '.[Team Id] AS [Team Id L', L.Level, '], ',
            'L', L.Level, '.[Team Name] AS [Team Name L', L.Level, ']')
            AS Item 
    ) CA
)

DECLARE @JoinItems NVARCHAR(MAX) = (
    SELECT STRING_AGG(CA.Item, '') WITHIN GROUP(ORDER BY Level)
    FROM (SELECT DISTINCT T.Level FROM Teams T WHERE T.Level > 0) L
    CROSS APPLY (
        SELECT CONCAT(
            CHAR(13), SPACE(4),
            'LEFT JOIN Teams L', L.Level,
            ' ON L', L.Level, '.Parent_Id = L', L.Level - 1, '.[Team Id]')
            AS Item 
    ) CA
)

DECLARE @Sql NVARCHAR(MAX) = '
    SELECT' + @SelectItems + '
    FROM Teams L0' + @JoinItems + '
    WHERE L0.Level = 0
'

--PRINT @SelectItems
--PRINT @JoinItems
PRINT @Sql

EXEC (@Sql)

(Новые строки CHAR(13) и отступы SPACE(8) присутствуют в печатном SQL только для эстетических целей.)

В приведенном выше примере используется функция STRING_AGG(), доступная в SQL Server 2017 и более поздних версиях. Для более ранних версий вы можете использовать метод, использующий комбинацию FOR XML PATH(''), TYPE, .value(...) и STUFF() для получения эквивалентных результатов.

ВАЖНО: При использовании динамического SQL вы всегда должны быть осторожны с возможностью SQL-инъекции, при которой ненадежный текст включается в динамический SQL небезопасным образом. Всякий раз, когда строковые данные из источника используются в качестве имени таблицы или имени столбца или его части, вы должны использовать QUOTENAME(name) для безопасного ввода этих имен. При внедрении строковых литералов используйте вариант QUOTENAME(value, '''').

В приведенном выше примере использования мы вводим только целочисленные значения, поэтому кавычки не требуются.

Сгенерированный SQL:

    SELECT
        L0.[Team Id] AS [Team Id L0], L0.[Team Name] AS [Team Name L0],
        L1.[Team Id] AS [Team Id L1], L1.[Team Name] AS [Team Name L1],
        L2.[Team Id] AS [Team Id L2], L2.[Team Name] AS [Team Name L2],
        L3.[Team Id] AS [Team Id L3], L3.[Team Name] AS [Team Name L3]
    FROM Teams L0
    LEFT JOIN Teams L1 ON L1.Parent_Id = L0.[Team Id]
    LEFT JOIN Teams L2 ON L2.Parent_Id = L1.[Team Id]
    LEFT JOIN Teams L3 ON L3.Parent_Id = L2.[Team Id]
    WHERE L0.Level = 0

Полученные результаты:

См. эту db<>fiddle для демонстрации, которая включает в себя как STRING_AGG(), так и FOR XML версии кода.