Hierarchical Data

SYS_CONNECT_BY_PATH

The SYS_CONNECT_BY_PATH function returns a single string containing all the column values encountered in the path from root to node.

select
  lpad(' ', level-1 ) || key        as key_indented ,
  name ,
  SYS_CONNECT_BY_PATH( key , '/' )  as key_path   ,
  SYS_CONNECT_BY_PATH( name, '/' )  as name_path
from
  t
start with
  parent_key is null
connect by
  parent_key = prior key
;
 
KEY_INDENTED    NAME       KEY_PATH                  NAME_PATH
--------------- ---------- ------------------------- -------------------------
nls             NLS        /nls                      /NLS
 demo           DATA       /nls/demo                 /NLS/DATA
 mesg           DEMO       /nls/mesg                 /NLS/DEMO
server          SERVER     /server                   /SERVER
 bin            BIN        /server/bin               /SERVER/BIN
 config         CONFIG     /server/config            /SERVER/CONFIG
  log           LOG        /server/config/log        /SERVER/CONFIG/LOG
 ctx            CTX        /server/ctx               /SERVER/CTX
  admin         ADMIN      /server/ctx/admin         /SERVER/CTX/ADMIN
  data          DATA       /server/ctx/data          /SERVER/CTX/DATA
   delx         DELX       /server/ctx/data/delx     /SERVER/CTX/DATA/DELX
   enlx         ENLX       /server/ctx/data/enlx     /SERVER/CTX/DATA/ENLX
   eslx         ESLX       /server/ctx/data/eslx     /SERVER/CTX/DATA/ESLX
  mig           MESG       /server/ctx/mig           /SERVER/CTX/MESG
 

SYS_CONNECT_BY_PATH is only available in Oracle version 9i or greater. For earlier versions a custom, recursive PL/SQL function can be used in place of SYS_CONNECT_BY_PATH.

------------------------------------------------------------
-- Note:
--
-- This function is only for demonstration purposes.
-- In a real application more robust code would be needed
-- to guard against things like separator characters
-- appearing in KEY values, hierarchical loops in the data,
-- etc.
------------------------------------------------------------

create or replace function KEY_PATH
(
  p_key       t.key%type ,
  p_separator varchar2 default '/'
)
  return varchar2
is

  v_parent_key t.parent_key%type ;
  v_key        t.key%type        ;

begin

  select parent_key, key
  into   v_parent_key, v_key
  from   t
  where  key = p_key ;

  if v_parent_key is null then return ( p_separator || v_key );
  else return ( KEY_PATH( v_parent_key, p_separator ) || p_separator || v_key );
  end if;

exception

  when no_data_found then return( null );

end;
/

show errors
No errors.
   
select
  lpad(' ', level-1 ) || key as key_indented ,
  name ,
  KEY_PATH( key, '/' ) as KEY_PATH
from
  t
start with
  parent_key is null
connect by
  parent_key = prior key
;
 
KEY_INDENTED    NAME       KEY_PATH
--------------- ---------- -------------------------
nls             NLS        /nls
 demo           DATA       /nls/demo
 mesg           DEMO       /nls/mesg
server          SERVER     /server
 bin            BIN        /server/bin
 config         CONFIG     /server/config
  log           LOG        /server/config/log
 ctx            CTX        /server/ctx
  admin         ADMIN      /server/ctx/admin
  data          DATA       /server/ctx/data
   delx         DELX       /server/ctx/data/delx
   enlx         ENLX       /server/ctx/data/enlx
   eslx         ESLX       /server/ctx/data/eslx
  mig           MESG       /server/ctx/mig
 

With this approach an additional function would be needed if the path for another column, like NAME, were required.

If no hierarchical information, e.g. LEVEL, other than a path is required then the START WITH and CONNECT BY clauses can be omitted since KEY_PATH already knows how to traverse the hierarchy.

select
  key ,
  name ,
  KEY_PATH( key, '/' ) as KEY_PATH
from
  t
order by
  KEY_PATH
;
 
KEY        NAME       KEY_PATH
---------- ---------- -------------------------
nls        NLS        /nls
demo       DATA       /nls/demo
mesg       DEMO       /nls/mesg
server     SERVER     /server
bin        BIN        /server/bin
config     CONFIG     /server/config
log        LOG        /server/config/log
ctx        CTX        /server/ctx
admin      ADMIN      /server/ctx/admin
data       DATA       /server/ctx/data
delx       DELX       /server/ctx/data/delx
enlx       ENLX       /server/ctx/data/enlx
eslx       ESLX       /server/ctx/data/eslx
mig        MESG       /server/ctx/mig
 



Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-12568.html]SQL Snippets: Hierarchical Data - SYS_CONNECT_BY_PATH[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-12568.html">SQL Snippets: Hierarchical Data - SYS_CONNECT_BY_PATH</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Hierarchical Data - SYS_CONNECT_BY_PATH
  • URL (href): http://www.sqlsnippets.com/en/topic-12568.html