Parent Child Relationship Flattening

Database

Parent Child Relationship Flattening

I need some help in flattening this data

Parent             Child            Level               Common_Key

=====          =====          =====            =========

WS              NULL         L0                     RRT

WS              WG1          L1                     RRT

WS               WG2         L1                     RRT

WS               WA          L1                     RRT

WG1              W1         L2                      RRT

WG1              B1          L2                      RRT

WG2              W2         L2                     RRT

WG2              B2          L2                    RRT

I want to flatten this dynamically as follows.  Level Column is not available.  I am illustrating it for understanding.  I tried to generate the level column using rank over partitioned by, it's not giving me the correct result. 

WS- WG1-W1-B1- WG22-W2-B2-WA

Any help will be great.  I do know how to solve it statically.  But I my Parent Child relationship is dynamic. 

5 REPLIES
Supporter

Re: Parent Child Relationship Flattening

You need to provide some test data - DDL & Insert statements
Beside this it is not an easy task...
What is your order rule? Seems not straight forward to me.
As you wane the whole tree down to the leaf and concat the different leafs afterwards it is not an easy task. So you would need to start from the childs who are not parents and concat all which belong to the same parent. And then move up.

Re: Parent Child Relationship Flattening

DATABASE XXXX;

CREATE MULTISET TABLE TEST123 ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT

(

COMMON_KEY VARCHAR(20),

PARENT VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,

CHILD VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,

LVL VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC

)

PRIMARY INDEX INUP00_CATEGY_FUNC ( PARENT, CHILD );

INSERT INTO TEST123

('WS', NULL, 0,'RRT');

INSERT INTO TEST123

('WS', 'WG1', 1,'RRT');

 

INSERT INTO TEST123

('WS','WG2', 1,'RRT');

 

INSERT INTO TEST123

('WS', 'WA', 1,'RRT');

INSERT INTO TEST123

('WG1', 'W1', 2,'RRT');

INSERT INTO TEST123

('WG1', 'B1', 2,'RRT');

INSERT INTO TEST123

('WG2', 'W2', 2,'RRT');

INSERT INTO TEST123

('WG2', 'B2', 2,'RRT');

 

Parent Child Level Common_Key

===== ===== ===== =========

WS NULL L0 RRT

WS WG1 L1 RRT

WS WG2 L1 RRT

WS WA L1 RRT

WG1 W1 L2 RRT

WG1 B1 L2 RRT

WG2 W2 L2 RRT

WG2 B2 L2 RRT

 

Desired Output

==============

WS WG1 WG2 WA W1 B1 W2 B2

Supporter

Re: Parent Child Relationship Flattening

But 

WS WG1 WG2 WA W1 B1 W2 B2

is not 

WSWG1-W1-B1WG22-W2-B2-WA

So, what is the final call on this?

Re: Parent Child Relationship Flattening

This is what is needed, sorry for the confusion

WS WG1 WG2 WA W1 B1 W2 B2

Supporter

Re: Parent Child Relationship Flattening

Your DDL seems not to be matching the inserts.

Below SQLs should do

CREATE SET TABLE TEST123 (
PARENT VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
CHILD VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
LVL VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
COMMON_KEY VARCHAR(20)
)
PRIMARY INDEX INUP00_CATEGY_FUNC ( PARENT, CHILD );

INSERT INTO TEST123('WS', NULL, 0,'RRT');
INSERT INTO TEST123('WS', 'WG1', 1,'RRT');
INSERT INTO TEST123('WS','WG2', 1,'RRT');
INSERT INTO TEST123('WS', 'WA', 1,'RRT');
INSERT INTO TEST123('WG1', 'W1', 2,'RRT');
INSERT INTO TEST123('WG1', 'B1', 2,'RRT');
INSERT INTO TEST123('WG2', 'W2', 2,'RRT');
INSERT INTO TEST123('WG2', 'B2', 2,'RRT');

create volatile table rn123
as
(
select parent,child,lvl,row_number() over (order by lvl,parent,child) as id
from test123
) with data
unique primary index (id)
on commit preserve rows
;

with recursive base ( id, string) as
(select id, cast(parent as varchar(100))
from rn123
where id = 1
UNION ALL
Select n.id, b.string !! ' ' !! n.child
from rn123 n
join
base b
on b.id + 1 = n.id
)
select string
from base
where (select max(id) from rn123) = id

Result: 

string

WS WA WG1 WG2 B1 W1 B2 W2

If you really need

WS WG1 WG2 WA W1 B1 W2 B2

you need to explain the order rule here...