Inner Join a variable number of times to expand child/parent relationships of mixed length

I have a table that links teams, departments and divisions together.

I would like to be able to 'stretch' this table into a wide view like below;

Whilst this is simply done using repeated inner joins when I know how many levels there are I would like to come up with something more generic so that the query would not need re-writing in the event that extra levels were added to the structure.

If Extra levels are added (lets limit it to 10 levels) then the result table would have columns all from Team Id L0, Team Id L2 through Team Id L10

e.g. if we added a London Layer below UK (level 3)

The output would become;

To date I have gone round in circles trying to use the pivot function which seems like the right thing to do but I just can't get it right. Likewise I have looked into recursive CTE's but that only seems to provide me with narrow tables instead of the wide one I am aiming for.

As others pointed out in the comments, you will need to use dynamic SQL. This is true any time the number of output columns is dependent on the input data and not known in advance.

The general technique is to:

1. Identify the repeating parts of the query. In your case, you need dynamic SQL for your select list and for your joins.
2. Generate those repeating SQL fragments with queries that tailor a template SQL fragment with values for each occurrence and aggregate those repeating fragments into a single string.
3. Combine those SQL fragments with the fixed SQL.
4. Execute the combine dynamic SQL using either EXEC (@Sql) or EXEC sp_executesql @Sql, '<parameter definitions>', <parameter-list>. (The latter is needed if you need to pass one or more parameters into or out of the dynamic SQL.)

Assuming that the top level team is always level 0 and that there are no gaps in the level numbers, the following should give you your desired results.

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)

(The newlines CHAR(13) and indentation SPACE(8) are present only for aesthetic purposes in the printed SQL.)

The above uses the STRING_AGG() function that is available in SQL Server 2017 and later. For earlier versions, you can use a technique that uses a combination for FOR XML PATH(''), TYPE, .value(...), and STUFF() to generate equivalent results.

IMPORTANT: When using dynamic SQL, you should always be on the lookout for the possibility of a SQL Injection vulnerability, where untrusted text is included in the dynamic SQL in an unsafe manner. Whenever a string data from the source is used as a table name of column name, or a part thereof, you should use QUOTENAME(name) to safely inject those names. If injecting string literals, use QUOTENAME(value, '''') variant.

For the above use case, we are only injecting integer values, so no quoting is necessary.

Generated 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

Results:

See this db<>fiddle for a demo that includes both STRING_AGG() and FOR XML versions of the code.