Geek asks and answers

Counting tree levels based on code in SQL

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

The table Product code consists of a code and description:

For instance:

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

The element level is calculated by counting the codes below it. The code below it should be contained in the current code. If that makes sense.

So in the example above:

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 solutions work well, although I think Nikola is a little faster! Thanks guys!

You will have to make a few changes 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
+3
source share
2 answers

. :

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
+4

. , SQL.

. . , .

, :

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
+4

More articles:


All Articles