need level1,level2,level3,level4,level5,level6 result by recursive or connect by query

Question

My account structure is tree based:

code	Title
1	abc
1-1	kkk
1-1-1	dfgf
1-1-1-1	dfsd
1-1-1-1-1-1	sdfsd
1-1-1-1-1-1-1	sccdfsd

I have 5000 records in this tree need result level wise

grand-fmother , father, child........

level1-tilte,level2-tilte,leve3-tilte,level4-tilte,level5-tilte,leve6-tilte,level7-tilte,level8-tilte,level9-tilte

How to get all parent title names level-wise in separate column?

Answer 1

You can detect number of elements in CODE column that defines levels of parenthood and use it in PIVOT to get the titles:

WITH      --  S a m p l e   D a t a :
    tbl AS
        ( Select '1' "CODE", 'abc' "TITLE" From Dual Union All
          Select '1-1' "CODE", 'kkk' "TITLE" From Dual Union All
          Select '1-1-1' "CODE", 'dfgf' "TITLE" From Dual Union All
          Select '1-1-1-1' "CODE", 'dfsd' "TITLE" From Dual Union All
          Select '1-1-1-1-1' "CODE", 'sdfsd' "TITLE" From Dual Union All
          Select '1-1-1-1-1-1' "CODE", 'sccdfsd' "TITLE" From Dual 
        )

--  S Q L :
Select  Max(TITLE_1) "TITLE_1", Max(TITLE_2) "TITLE_2", Max(TITLE_3) "TITLE_3", 
        Max(TITLE_4) "TITLE_4", Max(TITLE_5) "TITLE_5", Max(TITLE_6) "TITLE_6"
From    ( Select    CODE, TITLE, Length(CODE) - Length(Replace(CODE, '-')) + 1 "LVL"
          From  tbl
        )
        PIVOT( Max(TITLE) For LVL IN(1 "TITLE_1", 2 "TITLE_2", 3 "TITLE_3", 
                                     4 "TITLE_4", 5 "TITLE_5", 6 "TITLE_6",
                                     7 "TITLE_7", 8 "TITLE_8", 9 "TITLE_9")  )
Order By CODE

/*    R e s u l t :
TITLE_1 TITLE_2 TITLE_3 TITLE_4 TITLE_5 TITLE_6
------- ------- ------- ------- ------- -------
abc     kkk     dfgf    dfsd    sdfsd   sccdfsd   */

If you want to use connect by you can just change the inner sql like below:

Select  Max(TITLE_1) "TITLE_1", Max(TITLE_2) "TITLE_2", Max(TITLE_3) "TITLE_3", 
        Max(TITLE_4) "TITLE_4", Max(TITLE_5) "TITLE_5", Max(TITLE_6) "TITLE_6"
From    ( Select    CODE, TITLE, LEVEL "LVL"
          From  tbl
          Where LEVEL = Length(CODE) - Length(Replace(CODE, '-')) + 1
          Connect By LEVEL <= Length(CODE) - Length(Replace(CODE, '-')) + 1
        )
        PIVOT( Max(TITLE) For LVL IN(1 "TITLE_1", 2 "TITLE_2", 3 "TITLE_3", 
                                     4 "TITLE_4", 5 "TITLE_5", 6 "TITLE_6",
                                     7 "TITLE_7", 8 "TITLE_8", 9 "TITLE_9")  )
Order By CODE

The result is the same as above.

Answer 2

You can count the number of separators in the code hierarchy and determine the depth within the hierarchy from that and then use a hierarchical query to connect the rows and find all the titles using SYS_CONNECT_BY_PATH:

SELECT SUBSTR(SYS_CONNECT_BY_PATH(title, ', '), 3) AS titles
FROM   (
  SELECT code,
         title,
         LENGTH(code) - LENGTH(REPLACE(code, '-')) AS depth
  FROM   table_name
)
WHERE  CONNECT_BY_ISLEAF = 1
START WITH
       depth = 0
CONNECT BY
       code LIKE PRIOR code || '-%'
AND    depth = PRIOR depth + 1;

Which, for the sample data:

CREATE TABLE table_name (code, title) AS
  SELECT '1',           'abc'     FROM DUAL UNION ALL
  SELECT '1-1',         'kkk'     FROM DUAL UNION ALL
  SELECT '1-1-1',       'dfgf'    FROM DUAL UNION ALL
  SELECT '1-1-1-1',     'dfsd'    FROM DUAL UNION ALL
  SELECT '1-1-1-1-1',   'sdfsd'   FROM DUAL UNION ALL
  SELECT '1-1-1-1-1-1', 'sccdfsd' FROM DUAL;

Outputs:

TITLES
abc, kkk, dfgf, dfsd, sdfsd, sccdfsd

fiddle