matrix output

[zulu99]

Hi

we have foll columns ina out PBANk table

pbankname pbankparentcode pbankcode
aaa -1 101
bbb 101 201
ccc 101 202
ddd 101 203
eee -1 501
sss 501 333
qqq 501 121

bankcode are bracnhes whose parent are pbankparentcode .
-1 in pbamkparentcode indicates that it is parent bank.

i want an output something like this

pbankname (branch) pbanmcode(branch) pbankcode(parent) pbankname(parent)



Regards

[hrishy]

Hi

SQL> col pbankname format a10
SQL> col pbankparc format 9999
SQL> col pbanc format 9999
SQL> col parbankname format a10
SQL> select p1.pbankname pbankname ,p1.pbankparentcode pbankparc ,p1.pbankcode pbanc,
2 (select p2.pbankname from pbank p2
3 where pbankcode=p1.pbankparentcode) parbankname
4 from pbank p1
5
SQL> /

PBANKNAME PBANKPARC PBANC PARBANKNAM
---------- --------- ----- ----------
aaa -1 101
bbb 101 201 aaa
ccc 101 202 aaa
ddd 101 203 aaa
eee -1 501
sss 501 303 eee
qqq 501 121 eee

7 rows selected.

SQL> select * from pbank
2 /

PBANKNAME PBANKPARENTCODE PBANKCODE
---------- --------------- ----------
aaa -1 101
bbb 101 201
ccc 101 202
ddd 101 203
eee -1 501
sss 501 303
qqq 501 121

7 rows selected.

i think this si what you are lookin for..

regards
Hrishy

[hrishy]

Hi

well i am attaching the results in text format.because the forum is screwing up the formatting..:-)

regards
Hrishy

[Shestakov]

for any levels of a hierarchy (more then 1) method of solve this task has to be another:


create or replace type txt_nodes as table of varchar2(1000);
/

SQL> select lpad('.',(level-1)*2,'.') || '[' || to_char(a.node_id) || '-->' || a.txt ||']' node,
cast(multiset(select '[' || to_char(c.node_id) || '-->' || c.txt ||']'
from test_tree c
where a.node_id <> c.node_id
connect by prior c.root_id=c.node_id
start with c.node_id=a.node_id ) as txt_nodes) r
from test_tree a
connect by prior a.node_id=a.root_id
start with a.root_id=0;

NODE
R
------------------------- --------------------------------------------------------------------------------
[1-->node-1] TXT_NODES()
..[2-->node-b] TXT_NODES('[1-->node-1]')
....[5-->node-a] TXT_NODES('[2-->node-b]', '[1-->node-1]')
....[6-->node-e] TXT_NODES('[2-->node-b]', '[1-->node-1]')
..[3-->node-a] TXT_NODES('[1-->node-1]')
....[7-->node-f] TXT_NODES('[3-->node-a]', '[1-->node-1]')
......[13-->node-v] TXT_NODES('[7-->node-f]', '[3-->node-a]', '[1-->node-1]')
......[14-->node-a] TXT_NODES('[7-->node-f]', '[3-->node-a]', '[1-->node-1]')
....[8-->node-d] TXT_NODES('[3-->node-a]', '[1-->node-1]')
....[9-->node-a] TXT_NODES('[3-->node-a]', '[1-->node-1]')
..[4-->node-d] TXT_NODES('[1-->node-1]')
....[10-->node-a] TXT_NODES('[4-->node-d]', '[1-->node-1]')
......[15-->node-n] TXT_NODES('[10-->node-a]', '[4-->node-d]', '[1-->node-1]')
......[16-->node-m] TXT_NODES('[10-->node-a]', '[4-->node-d]', '[1-->node-1]')
......[17-->node-l] TXT_NODES('[10-->node-a]', '[4-->node-d]', '[1-->node-1]')
....[11-->node-x] TXT_NODES('[4-->node-d]', '[1-->node-1]')
....[12-->node-c] TXT_NODES('[4-->node-d]', '[1-->node-1]')
......[18-->node-k] TXT_NODES('[12-->node-c]', '[4-->node-d]', '[1-->node-1]')
......[19-->node-j] TXT_NODES('[12-->node-c]', '[4-->node-d]', '[1-->node-1]')

19 rows selected.

table test_tree has 3 filelds
node_id -- pk
root_if -- self reference
txt -- just any text

structure of tree :

SQL> select t.node_id, substr(lpad('.',(level-1)*2,'.') || t.txt,1,40) txt
from test_tree t
connect by prior t.node_id = t.root_id
start with t.node_id = 1;
2 3 4
NODE_ID TXT
---------- ----------------------------------------
1 node-1
2 ..node-b
5 ....node-a
6 ....node-e
3 ..node-a
7 ....node-f
13 ......node-v
14 ......node-a
8 ....node-d
9 ....node-a
4 ..node-d
10 ....node-a
15 ......node-n
16 ......node-m
17 ......node-l
11 ....node-x
12 ....node-c
18 ......node-k
19 ......node-j

19 rows selected.

[zulu99]

hi hrishy

thanx a lot it worked

[hrishy]

Hi shestakov

well dude i am quite impressed by your thought proceses..i would use your trick..:-)..that solution was cool dude

regards
Hrishy