How to get all the parents of a given node from a tree in single row?
HI,
I have a requirement to get the all the parent nodes till the root, of all the leaf nodes in a Tree structure in a single row instead of multiple rows.
Normally we can get the tree structure as follows for a leaf level node_id : 1023.
select node_id,node_name,node_level,parent_node_id
from tree
start with node_id=1023
connect by prior parent_node_id=node_id
This query returns all the parents of the leaf nodes, but in multiple rows as follows
1023, node_district, 4, 1022
1022, node_region, 3, 1021
1021, node_state, 2, 1020
1020, node_company, 1, 0
but my requirement is to get the node_names as follows in a single row.
node_company, node_state, node_region, node_district
How should I achieve this with a SQL query?
Thank you,
Srinivas Medukonduru
How to get all the parents of a given node from a tree in single row?
I am using Oracle 8.1.7.2.
Re: How to get all the parents of a given node from a tree in single row?
Code:
SELECT MAX(node_company),
MAX(node_state),
MAX(node_region),
MAX(node_district)
FROM
(SELECT DECODE(node_level,1,node_name) node_company,
DECODE(node_level,2,node_name) node_state,
DECODE(node_level,3,node_name) node_region,
DECODE(node_level,4,node_name) node_district
FROM
(select node_id,node_name,node_level,parent_node_id
from tree
start with node_id=1023
connect by prior parent_node_id=node_id
)
);
The assumption here is that each leaf node you are making the report for is at exactly fourth level, ie, it allways has three parent levels.
How we can achive it for multiple nodes without using the analytical functions?
Hi JModic,
Thank you for your query.
When I try the padder's query(which has the analytical functions),
the performance is very slow for large number of nodes(almost 1967 nodes) in the "in clause".
Then I have tried your query without analytical functions.
But I have one problem your query, that the nodes in the "IN clause" are not always level 4 nodes,
those may be any level from 4 to 1.
If the node in the "IN clause" is 4 then it returns 4,3,2,1.
If the node in the "IN clause" is 3 then it returns 3,2,1.
If the node in the "IN clause" is 2 then it returns 2,1.
If the node in the "IN clause" is 1 then it returns 1.
For the above scenario's CEIL(ROWNUM/4) doesn't work to group the results.
How we can acheive this without using the analytical functions?
Thanks in advance.
regards,
Srinivas Medukonduru