0

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:

  • 10s are their own parents since they are the roots
  • 90s are their own parents since they are the end of data
  • 20s parent is the previous 10
  • 21 30 31 32 33 parent is the previous 20
  • 40 and 50 parents is the previous 20
  • 41 parent is the previous 40

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

GSerg
  • 76,472
  • 17
  • 159
  • 346
AndreA
  • 779
  • 9
  • 22
  • 5
    You have obfuscated the problem so much it is very difficult to follow. Is the first set of data the actual table and the second set of data the desired output? And I don't understand your rules at all in comparison with your second set of data. – Sean Lange May 05 '22 at 16:12
  • Easily solved with `lag()` with `ignore nulls` which SQL Server does not support, but you can [emulate](https://stackoverflow.com/q/60105702/11683). – GSerg May 05 '22 at 16:37
  • @GSerg, no, it's not, because he has got irregular numbers in `parentId` field. – Bartłomiej Stasiak May 05 '22 at 16:55

2 Answers2

2

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;
James Casey
  • 2,447
  • 1
  • 11
  • 19
  • overcomplicated mess : ] let imagine maintaning code like that by other developers. creating of different table like i proposed will be more performant and easier to maintain, but as we see OP decided to write trash code in application – Bartłomiej Stasiak May 20 '22 at 01:10
  • He would still need to populate the column... which he could use my code to do? I agree it would be better as a column rather than calculating it on the fly every time. Without seeing the bigger picture of his application its hard to say how easy it would be to fill at the point of insert. I've updooted your post to bring balance to the force :) – James Casey May 20 '22 at 07:53
0

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

Bartłomiej Stasiak
  • 408
  • 2
  • 10