SQL Server 2012: flatten hierachy to dynamic number of columns In MS SQL Server SQL 2012, I have a hierarchy table with a dynamic number of levels. How can I transform the following table:

CREATE TABLE #Hierarchy (AccountId INT, ParentId INT, Name VARCHAR(50))

INSERT INTO #Hierarchy VALUES (1, NULL, 'Revenue')
INSERT INTO #Hierarchy VALUES (2, NULL, 'Costs')
INSERT INTO #Hierarchy VALUES (3, 1, 'Sales')
INSERT INTO #Hierarchy VALUES (4, 1, 'Interests')
INSERT INTO #Hierarchy VALUES (5, 2, 'COGS')
INSERT INTO #Hierarchy VALUES (6, 2, 'Expenses')
INSERT INTO #Hierarchy VALUES (7, 6, 'Salaries')
INSERT INTO #Hierarchy VALUES (8, 6, 'Other')


to get such a table:



Regards,
Przemek
I can't come up with anything better than building out a Dynamic SQL Statement for what you're trying to do.  This is neither easy to read nor efficient in execution, but it should do what you want and will work with any number of parent/child levels.  I'm also sure there are other ways to assemble this statement, so feel free to adjust this to your liking.

DB Fiddle for the following:

CREATE TABLE #Hierarchy (AccountId INT, ParentId INT, Name VARCHAR(50))
INSERT INTO #Hierarchy VALUES (1,NULL,'Revenue')
INSERT INTO #Hierarchy VALUES (2,NULL,'Costs')
INSERT INTO #Hierarchy VALUES (3,1,'Sales')
INSERT INTO #Hierarchy VALUES (4,1,'Interests')
INSERT INTO #Hierarchy VALUES (5,2,'COGS')
INSERT INTO #Hierarchy VALUES (6,2,'Expenses')
INSERT INTO #Hierarchy VALUES (7,6,'Salaries')
INSERT INTO #Hierarchy VALUES (8,6,'Other')


DECLARE @maxLevels INT

-- Retrieve Max Number of Levels From Hierarchy
;WITH depthCheck as
(
  SELECT H.AccountId, H.ParentId, Name, 0 AS Level
  FROM #Hierarchy H
  WHERE H.ParentId IS NULL

  UNION ALL

  SELECT Hc.AccountId, Hc.ParentId, Hc.Name, dc.Level + 1 AS Level
  FROM #Hierarchy Hc
  INNER JOIN depthCheck dC
  ON Hc.ParentId = dC.AccountId
 )
-- Add 1 Level because your column labels start with 1 instead of 0
SELECT @maxLevels = MAX(Level) + 1 From depthCheck

-- Declare some variables needed to assemble the Dynamic SQL Statement
DECLARE @qLevel INT = @maxLevels, @iLevel INT = @maxLevels, @sqlSelectStmt VARCHAR(MAX) = '', @sqlFromStmt VARCHAR(MAX) = ''

-- This nested While Loop builds out both custom SELECT and FROM clauses
WHILE @qLevel &gt; 0
BEGIN

    -- If Root level, the format will be different
    IF @qLevel = 1
    BEGIN
        SET @sqlSelectStmt = CHAR(9) + ', /\Name/\ AS Level_' + CAST(@qLevel AS VARCHAR(20)) + '_Name' + CHAR(13) + CHAR(10) + @sqlSelectStmt
        SET @sqlFromStmt = CHAR(9) + '#Hierarchy L' + CAST(@qLevel AS VARCHAR(20)) + CHAR(13) + CHAR(10) + @sqlFromStmt
    END
    -- If not a Root Level, COALESCE statements will be required for the SELECT clause and JOINS will be required in the FROM clause
    ELSE
    BEGIN
        SET @sqlSelectStmt = CHAR(9) + ', COALESCE(/\Name/\) AS Level_' + CAST(@qLevel AS VARCHAR(20)) + '_Name' + CHAR(13) + CHAR(10) + @sqlSelectStmt
        SET @sqlFromStmt = CHAR(9) + 'LEFT JOIN #Hierarchy L' + CAST(@qLevel AS VARCHAR(20)) + CHAR(13) + CHAR(10) + CHAR(9) + CHAR(9) 
                         + 'ON L' + CAST(@qLevel - 1 AS VARCHAR(20)) + '.AccountId = L' + CAST(@qLevel AS VARCHAR(20)) + '.ParentId' + CHAR(13) + CHAR(10) + @sqlFromStmt
    END

    -- Build out the cascading COALESCE statment
    WHILE @iLevel &gt; 0
    BEGIN
        SET @sqlSelectStmt = REPLACE(@sqlSelectStmt, '/\Name/\', 'L' + CAST(@iLevel AS VARCHAR(20)) + '.Name, /\Name/\')

        IF @iLevel = 1
            SET @sqlSelectStmt = REPLACE(@sqlSelectStmt, ', /\Name/\', '')

        SET @iLevel = @iLevel - 1
    END

    -- Decriment @qLevel and Reset @iLevel counters
    SET @qLevel = @qLevel - 1
    SET @iLevel = @qLevel
END

-- Include the AccId Column
SET @sqlSelectStmt = CHAR(9) + '  COALESCE(/\ID/\) AS AccId' + CHAR(13) + CHAR(10) + @sqlSelectStmt

-- Build out the cascading COALESCE statment for the AccId column
SET @iLevel = @maxLevels
WHILE @iLevel &gt; 0
BEGIN
    SET @sqlSelectStmt = REPLACE(@sqlSelectStmt, '/\ID/\', 'L' + CAST(@iLevel AS VARCHAR(20)) + '.AccountId, /\ID/\')

    IF @iLevel = 1
        SET @sqlSelectStmt = REPLACE(@sqlSelectStmt, ', /\ID/\', '')

    SET @iLevel = @iLevel - 1
END

--PRINT @sqlSelectStmt
--PRINT @sqlFromStmt

-- Assemble final Dynamic SQL Statement
DECLARE @finalSQL VARCHAR(MAX)
SET @finalSQL = 'SELECT' + CHAR(13) + CHAR(10) + @sqlSelectStmt
              + 'FROM' + CHAR(13) + CHAR(10) + @sqlFromStmt
              + 'WHERE' + CHAR(9) + 'L1.ParentId IS NULL'

-- Print for good measure
PRINT @finalSQL

-- Execute the assembled statement
EXEC(@finalSQL)

DROP TABLE #Hierarchy