The only part you're missing is a means to maintain the hierarchy/order once the data is inserted into the table.

You'll noticed that I added an IDENTITY column to the #Test_Data to fix the issue...

In any case, give this a look and make sure it's giving you what you need..

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#Test_Data','U') IS NOT NULL

DROP TABLE #Test_Data

--===== Create the test table with

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE #Test_Data

(

RowOrder INT NOT NULL IDENTITY(1,1),

[Entity] [nvarchar](255) NULL,

[IndentLevel] [float] NULL

)

--===== Insert the test data into the test table

INSERT INTO #Test_Data

(Entity,IndentLevel)

SELECT 'Lockheed Martin Corp','1' UNION ALL

SELECT 'Skunk Works - Roswell,NM','2' UNION ALL

SELECT 'LM Aviation','3' UNION ALL

SELECT 'Recon','4' UNION ALL

SELECT 'Facility 1 - LM Cost Center: 500 - Admin','5' UNION ALL

SELECT 'Facility 1 - LM Cost Center: 501 - Tech','5' UNION ALL

SELECT 'Facility 1 - LM Cost Center: 502 - Clerical','5' UNION ALL

SELECT 'Facility 1 - LM Cost Center: 503 - Acct','5' UNION ALL

SELECT 'Facility 1 - LM Cost Center: 504 - HR','5' UNION ALL

SELECT 'Fighter','4' UNION ALL

SELECT 'Facility 1 - LM Cost Center: 505 - Ammunition','5' UNION ALL

SELECT 'Facility 1 - LM Cost Center: 506 - Stuff','5' UNION ALL

SELECT 'Facility 1 - LM Cost Center: 507 - More Stuff','5' UNION ALL

SELECT 'Facility 1 - LM Cost Center: 508 - Cat5 Cables','5' UNION ALL

SELECT 'Facility 1 - LM Cost Center: 509 - Mops','5' UNION ALL

SELECT 'Bombers','4' UNION ALL

SELECT 'Facility 1 - LM Cost Center: 510 - Wings','5' UNION ALL

SELECT 'Facility 1 - LM Cost Center: 511 - HUD','5' UNION ALL

SELECT 'Facility 1 - LM Cost Center: 512 - Radar','5' UNION ALL

SELECT 'Facility 1 - LM Cost Center: 513 - Blackberry Phones','5' UNION ALL

SELECT 'Deep 6 - Norfolk, VA','2' UNION ALL

SELECT 'LM - Submarines','3' UNION ALL

SELECT 'Virginia Class Subs','4' UNION ALL

SELECT 'Facility 2 - LM Cost Center: 514 - Water','5' UNION ALL

SELECT 'Facility 2 - LM Cost Center: 515- Reactors','5' UNION ALL

SELECT 'Ohio Class Subs','4' UNION ALL

SELECT 'Facility 2 - LM Cost Center: 516 - Torpedos','5' UNION ALL

SELECT 'Facility 2 - LM Cost Center: 517 - Counter Measures','5' UNION ALL

SELECT 'Facility 2 - LM Cost Center: 518 - Bang and Olufsen party speakers','5'

GO

;WITH FindGroups AS (

SELECT

td.RowOrder,

td.Entity,

td.IndentLevel,

MAX(CASE WHEN td.IndentLevel = 1 THEN td.RowOrder END) OVER (ORDER BY td.RowOrder ROWS UNBOUNDED PRECEDING) L1,

MAX(CASE WHEN td.IndentLevel = 2 THEN td.RowOrder END) OVER (ORDER BY td.RowOrder ROWS UNBOUNDED PRECEDING) L2,

MAX(CASE WHEN td.IndentLevel = 3 THEN td.RowOrder END) OVER (ORDER BY td.RowOrder ROWS UNBOUNDED PRECEDING) L3,

MAX(CASE WHEN td.IndentLevel = 4 THEN td.RowOrder END) OVER (ORDER BY td.RowOrder ROWS UNBOUNDED PRECEDING) L4

FROM

#Test_Data td

)

SELECT

fg1.Entity AS [System],

fg2.Entity AS Facility,

fg3.Entity AS ServiceLine,

fg4.Entity AS Division,

fg5.Entity AS Department

FROM

FindGroups fg1

LEFT JOIN FindGroups fg2

ON fg1.L1 = fg2.L1

AND fg2.IndentLevel = 2

LEFT JOIN FindGroups fg3

ON fg2.L2 = fg3.L2

AND fg3.IndentLevel = 3

LEFT JOIN FindGroups fg4

ON fg3.L3 = fg4.L3

AND fg4.IndentLevel = 4

LEFT JOIN FindGroups fg5

ON fg4.L4 = fg5.L4

AND fg5.IndentLevel = 5

WHERE

fg1.IndentLevel = 1

The Results...

SystemFacilityServiceLineDivisionDepartment

----------------------------------------------------------------------------------------------------------------------------------------------

Lockheed Martin CorpSkunk Works - Roswell,NMLM AviationReconFacility 1 - LM Cost Center: 500 - Admin

Lockheed Martin CorpSkunk Works - Roswell,NMLM AviationReconFacility 1 - LM Cost Center: 501 - Tech

Lockheed Martin CorpSkunk Works - Roswell,NMLM AviationReconFacility 1 - LM Cost Center: 502 - Clerical

Lockheed Martin CorpSkunk Works - Roswell,NMLM AviationReconFacility 1 - LM Cost Center: 503 - Acct

Lockheed Martin CorpSkunk Works - Roswell,NMLM AviationReconFacility 1 - LM Cost Center: 504 - HR

Lockheed Martin CorpSkunk Works - Roswell,NMLM AviationFighterFacility 1 - LM Cost Center: 505 - Ammunition

Lockheed Martin CorpSkunk Works - Roswell,NMLM AviationFighterFacility 1 - LM Cost Center: 506 - Stuff

Lockheed Martin CorpSkunk Works - Roswell,NMLM AviationFighterFacility 1 - LM Cost Center: 507 - More Stuff

Lockheed Martin CorpSkunk Works - Roswell,NMLM AviationFighterFacility 1 - LM Cost Center: 508 - Cat5 Cables

Lockheed Martin CorpSkunk Works - Roswell,NMLM AviationFighterFacility 1 - LM Cost Center: 509 - Mops

Lockheed Martin CorpSkunk Works - Roswell,NMLM AviationBombersFacility 1 - LM Cost Center: 510 - Wings

Lockheed Martin CorpSkunk Works - Roswell,NMLM AviationBombersFacility 1 - LM Cost Center: 511 - HUD

Lockheed Martin CorpSkunk Works - Roswell,NMLM AviationBombersFacility 1 - LM Cost Center: 512 - Radar

Lockheed Martin CorpSkunk Works - Roswell,NMLM AviationBombersFacility 1 - LM Cost Center: 513 - Blackberry Phones

Lockheed Martin CorpDeep 6 - Norfolk, VALM - SubmarinesVirginia Class SubsFacility 2 - LM Cost Center: 514 - Water

Lockheed Martin CorpDeep 6 - Norfolk, VALM - SubmarinesVirginia Class SubsFacility 2 - LM Cost Center: 515- Reactors

Lockheed Martin CorpDeep 6 - Norfolk, VALM - SubmarinesOhio Class SubsFacility 2 - LM Cost Center: 516 - Torpedos

Lockheed Martin CorpDeep 6 - Norfolk, VALM - SubmarinesOhio Class SubsFacility 2 - LM Cost Center: 517 - Counter Measures

Lockheed Martin CorpDeep 6 - Norfolk, VALM - SubmarinesOhio Class SubsFacility 2 - LM Cost Center: 518 - Bang and Olufsen party speakers

Also... Nice work on setting up the OP! Having DDL & test data makes it much easier to focus on solutions. Thank you for that. 😀