Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Prashant.

Asked: January 13, 2022 - 4:58 pm UTC

Last updated: January 24, 2022 - 1:57 pm UTC

Version: 12c

Viewed 1000+ times

You Asked

Hello all,

Greetings of the new year!

Goal - generate an output which can be directly used for pasting hierarchical data into a flat file

Link to liveSQL with sample data and scripts - https://livesql.oracle.com/apex/livesql/s/mw9olr17o7dir21jysn5jni1u

Expected output

-#HDR#- 1 - HEADER1 - HEADER2 -
-#Line#- 1 - 1 - DETAIL11 - DETAIL12 ;
-#Line#- 1 - 2 - DETAIL21 - DETAIL22 ;


Hierarchy example:
Purchase Order HEADER - Only 1
Purchase Order LINE - 1-many under 1 HEADER
Purchase Order Schedule - 1-many under 1 LINE
Purchase Order Distribution - 1-many under 1 SCHEDULE


SQL included in LiveSQL currently produces the following:

-#HDR#- 1 - HEADER1 - HEADER2 -
-#Line#- 1 - 1 - DETAIL11 - DETAIL12 ;
-#HDR#- 1 - HEADER1 - HEADER2 -
-#Line#- 1 - 2 - DETAIL21 - DETAIL22 ;


Idea is to have a single column which shows the header and detail level information.
Assumption - single level detail. However, data exists with 3 levels of detail.

Request your inputs on generating expected output so that it can be extended to support multiple detail levels.

Thank you and stay safe!

BR,
Prashant ATMAN

and Chris said...

It's unclear to me how the hierarchy works when you add the lines and schedules. An example including these would help. But here's something that may help you get started:

- In a subquery concatenate together all the values for each line
- Assign a rank for each header
- Assign row number for each detail within each header
- Pivot header and detail columns resulting from this
- Exclude all the resulting header rows except for those with detail row # = 1

For example:

create table hdr ( a int, b varchar2(30), c varchar2(30) );
create table dtl (a int, b int, c varchar2(30), d varchar2(30));
insert into hdr values (1, 'HEADER1', 'HEADER2');
insert into dtl values (1, 1, 'DETAIL11', 'DETAIL12');
insert into dtl values (1, 2, 'DETAIL21', 'DETAIL22');

with rws as (
  select h.a || ' ' || h.b || ' ' || h.c hdr,
         d.b || ' ' || d.c || ' ' || d.d dtl,
         rank() over ( order by h.a ) hrk,
         row_number() over ( partition by h.a order by d.b ) drn
  from   hdr h
  join   dtl d
  on     ( h.a = d.a )
)
  select *
  from   rws
  unpivot ( 
    val for col in ( hdr, dtl ) 
  )
  where  ( drn = 1 or col = 'DTL' )
  order  by hrk, col desc, drn;

       HRK        DRN COL VAL                  
---------- ---------- --- ---------------------
         1          1 HDR 1 HEADER1 HEADER2    
         1          1 DTL 1 DETAIL11 DETAIL12  
         1          2 DTL 2 DETAIL21 DETAIL22  

Rating

  (2 ratings)

Comments

A reader, January 21, 2022 - 12:59 pm UTC

Thank you Chris!
Very handy, but I wanted to explore this further.

To give you a better example of the hierarchy, I have put together another set of data - https://livesql.oracle.com/apex/livesql/s/mykxd44zfdf8ilneyqcbslq9e

This example is for a total of 2 levels excluding header. The idea is to come up with a query to handle more than 2-3 levels so that it can extended to say 5-6 levels deep depending upon the data.

Hope, I was able to provide more clarity and thank you once again

Cheers!


Connor McDonald
January 24, 2022 - 2:51 am UTC

For the generic case of finding/aggregating all "children" below a parent, here's a pattern matching example on the EMP table, which shows the number of children under each "manager". Maybe you can use this as a template for your data

SQL> with raw_data as (
  2     select lvl, empno, ename, rownum as rn
  3     from ( select level as lvl, empno, ename
  4            from emp
  5            start with mgr is null
  6            connect by mgr = prior empno
  7            order siblings by empno  )
  8      )
  9  select empno
 10       , lpad(' ', (lvl-1)*2) || ename as ename
 11       , reports
 12  from raw_data
 13  match_recognize (
 14     order by rn
 15     measures
 16        starting_level.rn as rn
 17      , starting_level.lvl as lvl
 18      , starting_level.empno as empno
 19      , starting_level.ename as ename
 20      , count(higher_level.lvl) as reports
 21     one row per match
 22     after match skip to next row
 23     pattern (starting_level higher_level*)
 24     define  higher_level as lvl > starting_level.lvl
 25  )
 26  order by rn;

    EMPNO ENAME                   CHILDREN
--------- -------------------- ----------
     7839 KING                         13
     7566   JONES                       4
     7788     SCOTT                     1
     7876       ADAMS                   0
     7902     FORD                      1
     7369       SMITH                   0
     7698   BLAKE                       5
     7499     ALLEN                     0
     7521     WARD                      0
     7654     MARTIN                    0
     7844     TURNER                    0
     7900     JAMES                     0
     7782   CLARK                       1
     7934     MILLER                    0



Prashant, January 24, 2022 - 8:03 am UTC

thank you Chris and Connor!
Chris Saxon
January 24, 2022 - 1:57 pm UTC

You're welcome

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.