Here is the table:

CREATE TABLE test_table ( id integer not null, name char(1) not null, parent_id integer, sum integer not null); INSERT INTO test_table VALUES(1, 'A', null, 300); INSERT INTO test_table VALUES(11, 'B', 1, 2340); INSERT INTO test_table VALUES(111, 'C', 11, 3200); INSERT INTO test_table VALUES(22, 'D', 1, 7540); INSERT INTO test_table VALUES(2, 'E', null, 6300); INSERT INTO test_table VALUES(3, 'F', 2, 8600); INSERT INTO test_table VALUES(101, 'G', 3, 3800); INSERT INTO test_table VALUES(102, 'H', 3, 9700);`

Here is the code:

 SELECT sys_connect_by_path(name, ',') as Common FROM test_table START WITH parent_id is null CONNECT BY PRIOR id = parent_id`

It gives hierarchical nesting of names. How to change the code so that it shows only those lines that have parent_id , while only the name parent_id , excluding the line id ?

  • You have the beginning of the request somewhere gone. in the current version it does not display any id. And better show clearly how the desired result should look like - Mike
  • And the lines that have parent_id are apparently those that are not at the root of the tree ... then WHERE level>1 - Mike
  • in the current version, the query displays nested names depending on whether the corresponding string has an ancestor. Those. The output is something like this:, A; A, B; , A, b, c. And you need a type of output: " A; A, B. In other words, you need to show which ancestors this string has. - daydark
  • Those. the same full path sys_connect only as if the previous level? - Mike
  • It turns out that way. Formally, you need to delete the last two characters in the output line + first comma - daydark

1 answer 1

The prior keyword for data from the previous recursion level works not only with fields, but also with calculated values ​​and functions:

  select PRIOR sys_connect_by_path(name, ',') as Common from test_table WHERE level>1 -- Если нам не нужны корневые строки START WITH parent_id is null CONNECT BY PRIOR id = parent_id

Another solution without using PRIOR in a select:

select replace(substr(sys_connect_by_path(name, ','),2), name, '' ) as Common from test_table WHERE level>1 -- Если нам не нужны корневые строки START WITH parent_id is null CONNECT BY PRIOR id = parent_id