Skip to Main Content
  • Questions

  • How to print parent and children records on the same line?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Philip.

Asked: October 06, 2015 - 3:33 pm UTC

Last updated: October 13, 2015 - 1:28 am UTC

Version: 11.2.0.3.0

Viewed 1000+ times

You Asked

I have the following table:

create table fil00_components
(ac_type varchar2(3),
wuc varchar2(10),
serial varchar2(10),
tsn number(7,1),
unit_wuc varchar2(10),
unit_serial varchar2(10)
);

alter table fil00_components add primary key(ac_type, wuc, serial)
/

alter table fil00_components add foreign key(ac_type, unit_wuc, unit_serial) references fil00_components(ac_type, wuc, serial)
/

insert into fil00_components values('114', 'AA', '114051', 11877, null, null)
/

insert into fil00_components values('114', 'BE', '8111', 9059.5, 'AA', '114051')
/

insert into fil00_components values('114', 'BEB', 'K052', 8253.5, 'BE', '8111')
/

insert into fil00_components values('114', 'BEFF', 'CGA04570', 4361.7, 'BE', '8111')
/

insert into fil00_components values('114', 'AA', '114054', 10653, null, null)
/

insert into fil00_components values('114', 'BE', '8077', 9092.9, 'AA', '114054')
/

insert into fil00_components values('114', 'BEB', 'K2076', 6719.5, 'BE', '8077')
/

insert into fil00_components values('114', 'BEFF', '191', 8633.1, 'BE', '8077')
/

commit
/

Wuc value of 'AA' is the parent of wuc value 'BE' and 'BE' is the parent of both wuc values 'BEB' and 'BEFF'.
The desired out is to have the TSN values of grandparent, the parent and the children printed on the same line,
but only the grandparent serial is printed.

AA AA BE BEB BEFF
serial TSN TSN TSN TSN
----- ----- ----- ----- -----
114051 11877 9059.5 8253.5 4361.7
114054 10653.3 9092.9 6719.5 8633.1

How can I achieve this using a single SQL statement?

and Connor said...

I'm assuming the relationships are always present (if not, you just change to an outer join), but here's an example than you can modify to suit your need


SQL> with
  2  grandparents as
  3   ( select
  4        wuc
  5       ,serial
  6       ,tsn
  7       ,unit_wuc
  8       ,unit_serial
  9     from fil00_components where unit_wuc is null ),
 10  parents as
 11  ( select
 12        g.wuc  g_wuc
 13       ,g.serial g_serial
 14       ,g.tsn  g_tsn
 15       ,g.unit_wuc g_unit_wuc
 16       ,g.unit_serial g_unit_serial
 17       ,f.wuc p_wuc
 18       ,f.serial p_serial
 19       ,f.tsn p_tsn
 20       ,f.unit_wuc p_unit_wuc
 21       ,f.unit_serial p_unit_serial
 22    from fil00_components f,
 23         grandparents g
 24    where f.unit_wuc = g.wuc
 25    and   f.unit_serial = g.serial
 26  )
 27  select
 28        g_wuc
 29       ,g_serial
 30       ,g_tsn
 31       ,g_unit_wuc
 32       ,g_unit_serial
 33       ,p_wuc
 34       ,p_serial
 35       ,p_tsn
 36       ,p_unit_wuc
 37       ,p_unit_serial
 38       ,wuc
 39       ,serial
 40       ,tsn
 41       ,unit_wuc
 42       ,unit_serial
 43  from parents p,
 44       fil00_components f
 45  where f.unit_wuc = p.p_wuc
 46  and   f.unit_serial = p.p_serial;

G_WUC      G_SERIAL        G_TSN G_UNIT_WUC G_UNIT_SER P_WUC      P_SERIAL        P_TSN P_UNIT_WUC P_UNIT_SER WUC        SERIAL            TSN UNIT_WUC   UNIT_SERIA
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
AA         114051          11877                       BE         8111           9059.5 AA         114051     BEB        K052           8253.5 BE         8111
AA         114051          11877                       BE         8111           9059.5 AA         114051     BEFF       CGA04570       4361.7 BE         8111
AA         114054          10653                       BE         8077           9092.9 AA         114054     BEB        K2076          6719.5 BE         8077
AA         114054          10653                       BE         8077           9092.9 AA         114054     BEFF       191            8633.1 BE         8077

SQL>
SQL>
SQL>


Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, October 07, 2015 - 11:50 am UTC

Hi Connor,

Thank you for the quick response. Is there a way to print the grandparent, the parent, and the children on the same line?
The grandparent serial should be printed only once. Here is the output that I am interested in, which will be produced in Oracle Reports 6i.

grandparent serial TSN TSN TSN TSN
114051 11877 9059.5 8253.5 4361.7
114054 10653.3 9092.9 6719.5 8633.1
Connor McDonald
October 07, 2015 - 1:12 pm UTC

They *are* on the same line (even if the output here in the browser looks wrapped)

And you just edit the final SELECT to pick what columns you want.

A reader, October 07, 2015 - 2:06 pm UTC

Connor,

Thank you again for your quick reply. I should have checked your SQL in SQL*Plus. Silly me.
It's exactly what I was looking for.

You are awesome!

Alexander, October 07, 2015 - 2:36 pm UTC

If Tom ever checks in I wonder if he'll think, "what's going on here I finally take a break after a decade and now everyone provides a complete test case..." ;)
Chris Saxon
October 07, 2015 - 4:40 pm UTC

Haha :)

True many new questions do have a test case, we're still a way from all having one though...

using Connect by clause

Rajeshwaran, Jeyabal, October 09, 2015 - 1:49 pm UTC

Connor,

rewriting your sql using limited (needed) set of columns, i see this 

rajesh@ORA11G> column final_tsns format a20
rajesh@ORA11G> /

AC_ WUC        SERIAL            TSN UNIT_WUC   UNIT_SERIA FINAL_TSNS
--- ---------- ---------- ---------- ---------- ---------- --------------------
114 BEB        K052           8253.5 BE         8111       11877,9059.5,8253.5
114 BEFF       CGA04570       4361.7 BE         8111       11877,9059.5,4361.7
114 BEB        K2076          6719.5 BE         8077       10653,9092.9,6719.5
114 BEFF       191            8633.1 BE         8077       10653,9092.9,8633.1

4 rows selected.

rajesh@ORA11G> select t.*, trim(',' from sys_connect_by_path(tsn,',')) as final_tsns
  2  from fil00_components t
  3  where connect_by_isleaf = 1
  4  start with unit_wuc is null
  5  connect by prior wuc = unit_wuc
  6  and prior serial = unit_serial ;

AC_ WUC        SERIAL            TSN UNIT_WUC   UNIT_SERIA FINAL_TSNS
--- ---------- ---------- ---------- ---------- ---------- --------------------
114 BEB        K052           8253.5 BE         8111       11877,9059.5,8253.5
114 BEFF       CGA04570       4361.7 BE         8111       11877,9059.5,4361.7
114 BEB        K2076          6719.5 BE         8077       10653,9092.9,6719.5
114 BEFF       191            8633.1 BE         8077       10653,9092.9,8633.1

4 rows selected.

rajesh@ORA11G>


do you see any kind of issues with this connect by clause ? i couldn't find any,.
rajesh@ORA11G>
rajesh@ORA11G> select t.*, trim(',' from sys_connect_by_path(tsn,',')) as final_tsns
  2  from fil00_components t
  3  where connect_by_isleaf = 1
  4  start with unit_wuc is null
  5  connect by prior wuc = unit_wuc
  6  and prior serial = unit_serial ;

AC_ WUC        SERIAL            TSN UNIT_WUC   UNIT_SERIA FINAL_TSNS
--- ---------- ---------- ---------- ---------- ---------- --------------------
114 BEB        K052           8253.5 BE         8111       11877,9059.5,8253.5
114 BEFF       CGA04570       4361.7 BE         8111       11877,9059.5,4361.7
114 BEB        K2076          6719.5 BE         8077       10653,9092.9,6719.5
114 BEFF       191            8633.1 BE         8077       10653,9092.9,8633.1

4 rows selected.

rajesh@ORA11G>

using Connect by clause

Rajeshwaran, Jeyabal, October 09, 2015 - 1:55 pm UTC

Please ignore my previous post, here is the complete list.

Connor,

rewriting your sql using limited (needed) set of columns, i see this 

rajesh@ORA11G> with
  2    grandparents as
  3     ( select
  4          wuc
  5         ,serial
  6         ,tsn
  7         ,unit_wuc
  8         ,unit_serial
  9       from fil00_components where unit_wuc is null ),
 10    parents as
 11    ( select
 12          g.wuc  g_wuc
 13         ,g.serial g_serial
 14         ,g.tsn  g_tsn
 15         ,g.unit_wuc g_unit_wuc
 16         ,g.unit_serial g_unit_serial
 17         ,f.wuc p_wuc
 18         ,f.serial p_serial
 19         ,f.tsn p_tsn
 20         ,f.unit_wuc p_unit_wuc
 21         ,f.unit_serial p_unit_serial
 22      from fil00_components f,
 23           grandparents g
 24      where f.unit_wuc = g.wuc
 25      and   f.unit_serial = g.serial
 26    )
 27    select g_tsn
 28         ,p_tsn
 29         ,wuc
 30         ,tsn
 31         ,unit_wuc
 32         ,unit_serial
 33    from parents p,
 34         fil00_components f
 35    where f.unit_wuc = p.p_wuc
 36    and   f.unit_serial = p.p_serial;

     G_TSN      P_TSN WUC               TSN UNIT_WUC   UNIT_SERIA
---------- ---------- ---------- ---------- ---------- ----------
     11877     9059.5 BEB            8253.5 BE         8111
     11877     9059.5 BEFF           4361.7 BE         8111
     10653     9092.9 BEB            6719.5 BE         8077
     10653     9092.9 BEFF           8633.1 BE         8077

4 rows selected.

rajesh@ORA11G>


do you see any kind of issues with this connect by clause ? i couldn't find any,. 

rajesh@ORA11G> column final_tsns format a20
rajesh@ORA11G>
rajesh@ORA11G> select t.*, trim(',' from sys_connect_by_path(tsn,',')) as final_tsns
  2  from fil00_components t
  3  where connect_by_isleaf = 1
  4  start with unit_wuc is null
  5  connect by prior wuc = unit_wuc
  6  and prior serial = unit_serial ;

AC_ WUC        SERIAL            TSN UNIT_WUC   UNIT_SERIA FINAL_TSNS
--- ---------- ---------- ---------- ---------- ---------- --------------------
114 BEB        K052           8253.5 BE         8111       11877,9059.5,8253.5
114 BEFF       CGA04570       4361.7 BE         8111       11877,9059.5,4361.7
114 BEB        K2076          6719.5 BE         8077       10653,9092.9,6719.5
114 BEFF       191            8633.1 BE         8077       10653,9092.9,8633.1

4 rows selected.

rajesh@ORA11G>

Connor McDonald
October 09, 2015 - 11:34 pm UTC

There is no problem, but my inference from the original question was that

a) the relationship was only grandparent/parent/child
b) the extracted values needed to be in their own columns

We could take the sys_connect_by_patch output and parse it out into columns, but that seems a backward step (loss of datatypes etc).

But thank you for the alternative code - it lets our original poster decide whats best for his/her situation.


connect by ?!?!?!?

Rajeshwaran, Jeyabal, October 12, 2015 - 2:16 pm UTC

a) the relationship was only grandparent/parent/child

if that is the case, then 'connect by' become like this

connect by prior wuc = unit_wuc
and prior serial = unit_serial
and level <=3


is that correct ?
Chris Saxon
October 13, 2015 - 1:28 am UTC

Yes
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.