пятница, 18 марта 2016 г.

How to generate hierarchy query to string

select listagg(txt) within group (order by rn)
from (
  select
    isn, parentisn, rn,
    shortname ||
    decode(nxtlvl, lvl + 1, '(', '') ||
    decode(nxtlvl, lvl, ',', '') || 
    decode(nxtlvl, lvl + 1, '', lvl, '', ltrim(lpad(')', lvl - nxtlvl, ')'))) txt
  from (
    select t.*, lead(lvl) over(order by rn) nxtlvl
    from (
      select isn, parentisn, shortname, rownum rn, level lvl
      from dicti d
      start with isn = 8167
      connect by prior isn = parentisn
      order siblings by shortname) t))

Комментариев нет:

Отправить комментарий