CODEWITHSUNDEEP
oracle-database
vamsivanka
vamsivanka

Reputation: 792

Oracle Hierarchical Query output format

I have a table that list all the security project access numbers that you can possibly have for a given user. I am using Oracle db,

Security
---------
8055
8055.01
8056
8056.00
8056.01
8056.01.01
9005.01.02
9876.03

I am trying to get an output table in a format useful for our treeview application

Security       Parent ID    Group ID   Level No
------------------------------------------------
8055           null            1          1
8055.01        8055            1          2
8056           null            2          1
8056.00        8056            2          2 
8056.01        8056            2          2
8056.01.01     8056.01         2          3
9005.01.02     null            3          1
9876.03        null            4          1

can any one help me to get the above output format ?

Upvotes: 0

Views: 35

Answers (1)

MT0
MT0

Reputation: 168232

Oracle Setup:

CREATE TABLE Table_name ( Security ) AS
SELECT '8055' FROM DUAL UNION ALL
SELECT '8055.01' FROM DUAL UNION ALL
SELECT '8056' FROM DUAL UNION ALL
SELECT '8056.00' FROM DUAL UNION ALL
SELECT '8056.01' FROM DUAL UNION ALL
SELECT '8056.01.01' FROM DUAL UNION ALL
SELECT '9005.01.02' FROM DUAL UNION ALL
SELECT '9876.03' FROM DUAL;

Query:

WITH parents ( security, parent_id ) AS (
  SELECT security,
         SUBSTR( security, 1, INSTR( security, '.', -1 ) - 1  )
  FROM   table_name
)
SELECT security,
       PRIOR security AS parent_id,
       DENSE_RANK() OVER ( ORDER BY CONNECT_BY_ROOT( security ) ) AS group_id,
       LEVEL AS level_no
FROM   parents p
START WITH NOT EXISTS ( SELECT 'X'
                        FROM   table_name t
                        WHERE  t.security = p.parent_id )
CONNECT BY PRIOR security = parent_id
ORDER BY security;

Output:

SECURITY   PARENT_ID    GROUP_ID   LEVEL_NO
---------- ---------- ---------- ----------
8055                           1          1 
8055.01    8055                1          2 
8056                           2          1 
8056.00    8056                2          2 
8056.01    8056                2          2 
8056.01.01 8056.01             2          3 
9005.01.02                     3          1 
9876.03                        4          1

Upvotes: 1

Related Questions