CODEWITHSUNDEEP
sqloracle-databasegrouping-sets
diziaq
diziaq

Reputation: 7785

How to make SQL query with grouping in each node of an hierarchy tree?

There is a table with an usual "child-to-parent" structure, where each node has weight.

TREE_TABLE
----------------------------------------
   id  | parent_id  |  name    | weight
----------------------------------------
   1   |  NULL      |   N1     |   51
   2   |  1         |   N12    |   62
   3   |  1         |   N13    |   73
   4   |  2         |   N124   |   84
   5   |  2         |   N125   |   95

// for convenience the "name" column shows path from a node to the root node

How to build a SQL query that results in a set of rows where each row represents grouping per particular node and its children?

Please, use any SQL dialect on your choice. Just need a general idea or a prototype of the solution.

To solve this problem, I am experimenting with GROUPING SETS and ROLLUP report, but can't figure out the way of handling "dynamic" number of grouping levels.

Example of expected query result:

RESULT
--------------------------
 name  |  summary_weight
--------------------------
  N1   |   (51+62+73+84+95)
  N12  |   (62+84+95)    
  N124 |   (84)
  N125 |   (95)
  N13  |   (73)

Upvotes: 0

Views: 447

Answers (2)

Krasnoslobodtsev Sergei
Krasnoslobodtsev Sergei

Reputation: 236

for example:

with 
 datum(id,parent_id,name,weight)
 as
 (
 select 1,NULL,'N1',51 from dual union all
 select 2 ,  1 , 'N12' , 62  from dual union all
 select 3 ,  1 , 'N13' , 73 from dual union all
 select 4 ,  2 , 'N124' , 84 from dual union all
 select 5 ,  2 , 'N125' , 95 from dual
 ),
 step1 as
 (
 select id,parent_id,name,weight, connect_by_root name  root,connect_by_isleaf is_parent 
 from datum
 connect by prior id = parent_id 
 )
select root,sum(weight)  sum_w,
 '('||listagg(weight,'+') within group(order by null) ||')' str_w,
 '('||listagg(name,'+') within group(order by null) ||')' str_n
from step1
group by root
order by 1;

enter image description here

link: Hierarchical Queries

Upvotes: 1

Rui Costa
Rui Costa

Reputation: 437

If you want to SUM up, you can use this option:

SELECT 
  name, 
  (SELECT 
     sum(t2.weight) 
   FROM tree t2 
   WHERE t2.name LIKE t1.name || '%' )
FROM tree t1
ORDER BY rpad(name,10,'0');

If you want to concatenate the text, you can use this:

SELECT 
  name, 
  (SELECT 
    '(' || string_agg(t2.weight || '','+') || ')' 
   FROM tree t2 
   WHERE t2.name LIKE t1.name || '%' )
FROM tree t1
ORDER BY rpad(name,10,'0');

Upvotes: 1

Related Questions