CODEWITHSUNDEEP
sql
Neil
Neil

Reputation: 227

Counting tree levels based on a code in SQL

I have a data table that contains data which is technically a tree structure, but the tree is defined by a code and the length of this code.

The Commodity Code table consists of a code and a description:

For example:

Code    Description
------  -------------
0101    Live Animals
01011   Horses
010110  Purebred
010190  Other

The level of an item is calculated by counting the codes beneath it. The code beneath it must be contained in the current code. If that makes any sense.

So in the above example:

0101   is level 0 (nothing is contained in it)
01011  is level 1 (0101 is contained in it)
010110 is level 2 (0101 and 01011 is contained in it)
010190 is level 1 (only 0101 is contained in it)

Is there a way to get these levels in SQL? I am using DB2.

EDIT: Both Nikola and Gordon's solutions work well, although I think Nikola's is slightly quicker! Thank you guys!

Had to make a few edits to account for DB2:

select
    t1.code, count(t2.code)
from commoditycode t1 
left join commoditycode t2
on substr(t1.code, 1, length(t1.code) - 1) like concat(t2.code, '%')
group by t1.code

Upvotes: 3

Views: 367

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269713

Interesting problem. If I understand it correctly, this is solvable using standard SQL.

Here is the idea. For each code, I want to compare it to all other codes. I only care about other codes where the beginning of the first code matches the entire other code.

Once I have this, I just count the number of such codes:

select code, description, count(*) as level
from 
(
  select c.code, c.description, c2.code as abovecode
  from commmodity c 
  left outer join commodity c2 on
    on left(c.code, len(c2.code)) = c2.code 
    and c.code <> c2.code
  group by c.code, c2.code
) c
group by code, description

Upvotes: 4

Nikola Markovinović
Nikola Markovinović

Reputation: 19356

A join to itself on code minus last character will find all parents on the right side. Counting them will get the level of item:

declare @test table (code varchar(10), name varchar(100))

insert into @test values ('0101', 'Live Animals')
insert into @test values ('01011', 'Horses')
insert into @test values ('010110', 'Purebred')
insert into @test values ('010190', 'Other')

select t1.code, t1.name, count (t2.code) + 1 [level]
  from @test t1
  left join @test t2
    on substring (t1.code, 1, len (t1.code) - 1) like t2.code + '%'
 group by t1.code, t1.name


code    name            level
01011   Horses          2
0101    Live Animals    1
010190  Other           2
010110  Purebred        3

Upvotes: 4

Related Questions