DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: matrix output

  1. #1
    Join Date
    Dec 2002
    Posts
    110

    matrix output

    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

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    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

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

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

    regards
    Hrishy

  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    for any levels of a hierarchy (more then 1) method of solve this task has to be another:

    Code:
    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.

  5. #5
    Join Date
    Dec 2002
    Posts
    110

    thanx

    hi hrishy

    thanx a lot it worked

  6. #6
    Join Date
    Jan 2001
    Posts
    2,828
    Hi shestakov

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

    regards
    Hrishy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width