CODEWITHSUNDEEP
sqlsql-servert-sqlparent-childhierarchy
AndreA
AndreA

Reputation: 789

T-SQL creating a hierarchy out of orderly numbers

I have such table:

Id  code
1   10
2   11
3   20
4   21
5   30
6   31
7   32
8   40
9   10
10  11
11  20
12  21
13  30
14  31
15  32
16  40
17  20
18  21
19  30
20  31
21  32
22  40
23  20
24  21
25  30
26  31
27  32
28  40
29  20
30  21
31  30
32  31
33  32
34  40
35  20
36  21
37  30
38  31
39  32
40  40
41  41
42  90

The column id represents simply the order of the records.
The column code represent the type of record.

The problem is that the records are part of a hierarchy, as shown here:

hierarchy

What I need to obtain is the parent of every record:

Id  code Parent
1   10  1
2   11  1
3   20  1
4   21  3
5   30  3
6   31  3
7   32  3
8   40  3
9   10  9
10  11  9
11  20  9
12  21  11
13  30  11
14  31  11
15  32  11
16  40  11
17  20  9
18  21  17
19  30  17
20  31  17
21  32  17
22  40  17
23  20  9
24  21  23
25  30  23
26  31  23
27  32  23
28  40  23
29  20  9
30  21  29
31  30  29
32  31  29
33  32  29
34  40  29
35  20  9
36  21  35
37  30  35
38  31  35
39  32  35
40  40  35
41  41  40
42  90  42

The parent of every record should be expressed as its Id.

The rules are like this:

As you can see the order in which records are is very important.

I tried to solve this declaratively (with lag() etc) and imperatively with loops but I could not find a solution.

Please help

Upvotes: 0

Views: 81

Answers (2)

James Casey
James Casey

Reputation: 2507

This should work. Probably not optimal performance, but its pretty clear what its doing so should be easy to modify if (when!) your hierarchy changes.

It can obviously produce nulls if your hierarchy or ordering is not as you have prescribed

CREATE TABLE #data(id INT, code INT);
INSERT INTO #data values
(1  , 10),(2  , 11),(3  , 20),(4  , 21),(5  , 30),(6  , 31),(7  , 32),(8  , 40),(9  , 10),(10 , 11),
(11 , 20),(12 , 21),(13 , 30),(14 , 31),(15 , 32),(16 , 40),(17 , 20),(18 , 21),(19 , 30),(20 , 31),
(21 , 32),(22 , 40),(23 , 20),(24 , 21),(25 , 30),(26 , 31),(27 , 32),(28 , 40),(29 , 20),(30 , 21),
(31 , 30),(32 , 31),(33 , 32),(34 , 40),(35 , 20),(36 , 21),(37 , 30),(38 , 31),(39 , 32),(40 , 40),
(41 , 41),(42 , 90);

WITH 
tens AS (SELECT id FROM #data WHERE code = 10),
twenties AS (SELECT id FROM #data WHERE code = 20),
forties AS (SELECT id FROM #data WHERE code = 40)
SELECT #data.id, 
    #data.code, 
    CASE WHEN code IN (10,90) THEN #data.id     
        WHEN code IN (11,20) THEN prev_ten.id
        WHEN code IN (21,30,31,32,33,40,50) THEN prev_twenty.id
        WHEN code = 41 THEN prev_forty.id
        ELSE NULL 
    END AS Parent
FROM #data
OUTER APPLY (SELECT TOP (1) id FROM tens WHERE tens.id < #data.id ORDER BY tens.id DESC) AS prev_ten
OUTER APPLY (SELECT TOP (1) id FROM twenties WHERE twenties.id < #data.id ORDER BY twenties.id DESC) AS prev_twenty
OUTER APPLY (SELECT TOP (1) id FROM forties WHERE forties.id < #data.id ORDER BY forties.id DESC) AS prev_forty;

Upvotes: 2

Bartłomiej Stasiak
Bartłomiej Stasiak

Reputation: 518

i think u should add FOREIGN KEY parentId referencing Id to existing table, fill this new column by UPDATE or gain data to fill it from external source and then u should do SELECT * FROM tableName ORDER BY parentId to receive tree structure

Upvotes: 0

Related Questions