Create parent/child relation in SQL server based on the below scenario

In this picture, 2018_9UKDT – UKD Expense Total is parent of 2018_9Q400 – UKD Indirects and this in turn is parent to 98064 – IT SDS Costs, 98063 – ACS in charges, 98012 – UKD – Central.

Could someone let me know how to create parent child relation using T-SQL which has upto 10 levels.

Table creation

create table Test
(
    ID INT IDENTITY(1,1),
    NAME VARCHAR (255),
    Level Varchar(25)
);

insert into test (name) values
('     2018_9UKDT - UKD Expense Total'),
('          2018_9Q400 - UKD Indirects'),
('               98064 - IT SDS Costs'),
('               98063 - ACS in charges'),
('               98012 - UKD - Central'),
('          2018_9Q300 - UKD Non Opex Total');

and the output would be in the below attached screen shot. Basically from least level to increasing order.

Answer

Since you know you have a max level of 10, you can use a couple of common table expressions and conditional aggregation:

With CTE1 AS 
(
SELECT  Id, 
        name, 
        DENSE_RANK() OVER(ORDER BY LEN(name) - LEN(LTRIM(name)) DESC) As Level
FROM Test
), CTE2 AS
(
SELECT  Id, 
        Name, 
        Level,
        ISNULL((SELECT MIN(ID)
         FROM CTE1 As t1
         WHERE T1.Level > T0.Level 
         ), Id) As ParentId
FROM CTE1 As T0
)

SELECT MAX(CASE WHEN Level = 1 THEN Trimmed END) As Level1
      ,MAX(CASE WHEN Level = 2 THEN Trimmed END) As Level2
      ,MAX(CASE WHEN Level = 3 THEN Trimmed END) As Level3
      ,MAX(CASE WHEN Level = 4 THEN Trimmed END) As Level4
      -- more of the same
FROM CTE2
CROSS APPLY
(SELECT LTRIM(RTRIM(Name)) As Trimmed) As DRY
GROUP BY ParentId

Results (for the sample data posted on the question):

    Level1                      Level2                          Level3                              Level4
    98064 - IT SDS Costs        2018_9Q400 - UKD Indirects      2018_9UKDT - UKD Expense Total      NULL

Please note that for multiple root nodes, it will be difficult to know where the root node is (basically, you could have Level1, Level2 … as null, where at least one other node have more children.

Therefor, you should probably have the root node on the left most column. To do that, change the dense_rank order by from DESC to ASK, and the condition on the subquery in the second cte from WHERE T1.Level > T0.Level to WHERE T1.Level < T0.Level.