add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Teams (
[Team Id] INT,
Parent_Id INT,
[Team Name] VARCHAR(10),
Level INT
)
INSERT Teams
VALUES
(1, 4, 'UK', 2),
(2, 4, 'France', 2),
(3, 5, 'Japan', 2),
(4, 6, 'Europe', 1),
(5, 6, 'Asia', 1),
(6, NULL, 'Global', 0),
(7, 1, 'London', 3)
7 rows affected
-- SQL Server 2017 and later
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)

Team Id L0 Team Name L0 Team Id L1 Team Name L1 Team Id L2 Team Name L2 Team Id L3 Team Name L3
6 Global 4 Europe 1 UK 7 London
6 Global 4 Europe 2 France null null
6 Global 5 Asia 3 Japan null null 

    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
-- Earlier SQL Server versions
DECLARE @SelectItems NVARCHAR(MAX) = (
STUFF((
SELECT ',' + CA.Item
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
ORDER BY Level
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
, 1, 1, '') -- Strip the initial comma
)

DECLARE @JoinItems NVARCHAR(MAX) = (
(
SELECT CA.Item
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
ORDER BY L.Level
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
)

DECLARE @Sql NVARCHAR(MAX) = '
SELECT' + @SelectItems + '
Team Id L0 Team Name L0 Team Id L1 Team Name L1 Team Id L2 Team Name L2 Team Id L3 Team Name L3
6 Global 4 Europe 1 UK 7 London
6 Global 4 Europe 2 France null null
6 Global 5 Asia 3 Japan null null 

    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