CODEWITHSUNDEEP
sqlsql-servert-sql
Babulal
Babulal

Reputation: 369

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

enter image description here

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.

enter image description here

Upvotes: 0

Views: 211

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82524

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.

Upvotes: 1

Related Questions