How to get all the parents of a given node from a tree in single row?

[jmodic]

Originally posted by srinivasm
How we can achive it for multiple leaf nodes ?

Look at padders's suggestions. In case for some reason you can't (or won't) use analytical functions, you can still use my method, only slightly modified:

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,
         CEIL(ROWNUM/4) bucket
  FROM
   (SELECT node_id,node_name,node_level,parent_node_id
    FROM tree
    START WITH node_id IN (1023,1080)
    CONNECT BY PRIOR parent_node_id=node_id
   )
 )
GROUP BY bucket;