This discussion is archived
3 Replies Latest reply: Mar 26, 2013 11:43 AM by Frank Kulash RSS

Group like tree in conceptual view

Ayham Newbie
Currently Being Moderated
Hi to all,
i have groups in company, these groups includes names of emplyess.
i want to show these groups in tree conceptual only that mean who is the emplyee includes in each group that more frequenet name of emplyee . this emplyee sholude be on top at level one. and so on.it is like oragnization charts.



drop table groups1 ;
create table groups1 (emp_id number(9) , name varchar2(20), group_val varchar2(20));
insert into groups1 values(1,'Ahmed','goup1');
insert into groups1 values(2,'Ali','goup1');
insert into groups1 values(3,'Omar','goup1');
insert into groups1 values(4,'Amr','goup1');
insert into groups1 values(5,'Ahmed','goup2');
insert into groups1 values(6,'Abdullah','goup2');
insert into groups1 values(7,'Amr','goup2');
insert into groups1 values(8,'Mohamed','goup3');
insert into groups1 values(9,'Hussin','goup3');
insert into groups1 values(10,'Ahmed','goup3');
insert into groups1 values(11,'Gamal','goup4');
insert into groups1 values(12,'Kamal','goup4');
insert into groups1 values(13,'Ali','goup5');
insert into groups1 values(14,'Hussin','goup5');
insert into groups1 values(15,'Amr','goup5');
insert into groups1 values(16,'Khalid','goup5');
insert into groups1 values(17,'Omar','goup5');
insert into groups1 values(19,'Hassan', 'goup6');
insert into groups1 values(20,'Mahmoud','goup6');
insert into groups1 values(21,'Ahmed', 'goup7');
insert into groups1 values(22,'Amr','goup7');
insert into groups1 values(23,'Hassan', 'goup8');
insert into groups1 values(24,'Bogamal','goup8');
insert into groups1 values(25,'Amal','goup8');
insert into groups1 values(26,'Hassan', 'goup9');
insert into groups1 values(27,'Moien','goup9');
insert into groups1 values(28,'nader','goup9');
insert into groups1 values(29,'Hassan', 'goup10');
insert into groups1 values(30,'Waheeb','goup10');
insert into groups1 values(31,'Waheeb','goup11');
insert into groups1 values(32,'Rami','goup11');
commit;
the follwoing to show the occuramce of name in each group
SQL> select name,count(*) cn from groups1 group by name order by cn desc;

NAME                         CN
-------------------- ----------
Amr                           4
Ahmed                         4
Hassan                        4
Omar                          2
Ali                           2
Waheeb                        2
Hussin                        2
Amal                          1
Moien                         1
Khalid                        1
Kamal                         1
Gamal                         1
Mohamed                       1
Mahmoud                       1
nader                         1
Abdullah                      1
Rami                          1
Bogamal                       1

18 rows selected.
the follwoing to show levels , these levels according to number of occurance of names
name                       frequent         level
Amr                           4      Level 1 
Ahmed                         4      Level 1 
Hassan                        4      Level 1 
Omar                          2      Level 2 
Ali                           2      Level 2
Waheeb                        2      Level 2
Hussin                        2          Level 2
Amal                          1      Level 3
Moien                         1      Level 3
Khalid                        1      Level 3
Kamal                         1          Level 3
Gamal                         1      Level 3
Mohamed                       1      Level 3
Mahmoud                       1      Level 3
nader                         1             Level 3
Abdullah                      1      Level 3
Rami                          1      Level 3
Bogamal                       1      Level 3
the follwoing is to show for each name in which level and belong to which level
Amr                           4  Level 1a  ----> Belong to Root
Ahmed                         4  Level 1a  ----> Belong to Root
Hassan                        4  Level 1b  ----> Belong to Root
Omar                          2  Level 2a  ----> Belong Level 1a
Ali                           2  Level 2a  ----> Belong Level 1a
Waheeb                        2  Level 2c  ----> Belong Level 1b
Hussin                        2      Level 2d  ----> Belong Level 1a
Amal                          1  Level 3a  ----> Belong Level 1b
Moien                         1  Level 3b  ----> Belong Level 1b
Khalid                        1  Level 3c  ----> Belong Level 2a
Kamal                         1      Level 3d  ----> Belong to Root
Gamal                         1  Level 3e  ----> Belong to Root
Mohamed                       1  Level 3f  ----> Belong Level 2d
Mahmoud                       1  Level 3g  ----> Belong Level 1b
nader                         1  Level 3h  ----> Belong Level 1b
Abdullah                      1  Level 3i  ----> Belong Level 1a
Rami                          1  Level 3j  ----> Belong Level 2c
Bogamal                       1  Level 3k  ----> Belong Level 1b
the follwoing is the expected output is
name         group_name      belong_group

Amr              1                   root                         
Ahmed            1                      root
Hassan           2                       root
Omar             3                     1                             
Ali              3                     1                              
Waheeb           4                     2                            
Hussin           5                     1                           
Amal             6                     2                    
Moien            7                         2     
Khalid           8                     3                  
Kamal            9                    root                  
Gamal            10                   root                  
Mohamed          11                    5                  
Mahmoud          12                    2                
nader            13                    2                 
Abdullah         14                    1                 
Rami             15                    4                
Bogamal          16                    2                  
Many thanks for all
  • 1. Re: Group like tree in conceptual view
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    Ayham wrote:
    drop table groups1 ;
    create table groups1 (emp_id number(9) , name varchar2(20), group_val varchar2(20));
    insert into groups1 values(1,'Ahmed','goup1'); ...
    Thanks for posting the CREATE TABLE and INSERT statements; that's very helpful!
    the follwoing to show the occuramce of name in each group
    SQL> select name,count(*) cn from groups1 group by name order by cn desc;
    
    NAME                         CN
    -------------------- ----------
    Amr                           4
    Ahmed                         4
    Hassan                        4
    Omar                          2
    Ali                           2
    Waheeb                        2
    Hussin                        2
    Amal                          1
    Moien                         1
    ...
    Let's call the result set above RS1 (short for "Result Set 1") and the set below RS2.
    the follwoing to show levels , these levels according to number of occurance of names
    name                       frequent         level
    Amr                           4      Level 1 
    Ahmed                         4      Level 1 
    Hassan                        4      Level 1 
    Omar                          2      Level 2 
    Ali                           2      Level 2
    Waheeb                        2      Level 2
    Hussin                        2          Level 2
    Amal                          1      Level 3
    Moien                         1      Level 3
    ...
    LEVEL is an Oracle pseudo-column, so it's not a good column name. Let's call the last column in RS2 lvl instead.
    Explain how you get lvl.
    For example "Frequent in RS2 is the same as CN in RS1. Everyone with the same value of frequent will have the same value of lvl . Since 4 is the highest value of frequent, everyone who has frequent=1 will have lvl="Level 1". Since 2 happens to be the 2nd highest value of frequent, then everyone with frequencet=2 will have lvl='Level 2'. ..."
    If that's how you define lvl, then you can use <tt> DESNE_RANK () OVER (ORDER BY frequent DESC) </tt> to derive it.
    the follwoing is to show for each name in which level and belong to which level
    Amr                           4  Level 1a  ----> Belong to Root
    Ahmed                         4  Level 1a  ----> Belong to Root
    Hassan                        4  Level 1b  ----> Belong to Root
    Omar                          2  Level 2a  ----> Belong Level 1a
    Ali                           2  Level 2a  ----> Belong Level 1a
    Waheeb                        2  Level 2c  ----> Belong Level 1b
    Hussin                        2      Level 2d  ----> Belong Level 1a
    Amal                          1  Level 3a  ----> Belong Level 1b
    Moien                         1  Level 3b  ----> Belong Level 1b
    Khalid                        1  Level 3c  ----> Belong Level 2a
    Kamal                         1      Level 3d  ----> Belong to Root
    Gamal                         1  Level 3e  ----> Belong to Root
    Mohamed                       1  Level 3f  ----> Belong Level 2d
    Mahmoud                       1  Level 3g  ----> Belong Level 1b
    nader                         1  Level 3h  ----> Belong Level 1b
    Abdullah                      1  Level 3i  ----> Belong Level 1a
    Rami                          1  Level 3j  ----> Belong Level 2c
    Bogamal                       1  Level 3k  ----> Belong Level 1b
    Let's cll this RS3.
    How do you get the letter ('a', 'b', and so on) concatenated to lvl?
    Is the first part of that column the same as lvl from RS2?
    Why do you want the results above, and not
    Amr                4  Level 1a
    Ahmed              4  Level 1a
    Hassan             4  Level 1a
    or
    Amr                4  Level 1a
    Ahmed              4  Level 1b
    Hassan             4  Level c
    or
    Amr                4  Level 1a
    Ahmed              4  Level 1c
    Hassan             4  Level 1c
    ?
    Why is there no 'Level 2b'?
    What does "belong" mean? Will a row with 'Level 1x' always belong to 'Root'? Will rows with 'Level Ny' (N > 1) always belong to 'Level Mx' ( M , N) or to 'Root'?
    the follwoing is the expected output is
    name         group_name      belong_group
    
    Amr              1                   root                         
    Ahmed            1                      root
    Hassan           2                       root
    Omar             3                     1                             
    Ali              3                     1                              
    Waheeb           4                     2                            
    Hussin           5                     1                           
    Amal             6                     2                    
    Moien            7                         2     
    Khalid           8                     3                  
    Kamal            9                    root                  
    Gamal            10                   root                  
    Mohamed          11                    5                  
    Mahmoud          12                    2                
    nader            13                    2                 
    Abdullah         14                    1                 
    Rami             15                    4                
    Bogamal          16                    2                  
    It looks like group_name can be computed with DENSE_RANK.
    Many thanks for all
  • 2. Re: Group like tree in conceptual view
    Ayham Newbie
    Currently Being Moderated
    Many thanks for you Frank.

    what i need only the following results only: the others was as explanation to help anybody
    name         group_name      belong_group
     
    Amr              1                   root                         
    Ahmed            1                      root
    Hassan           2                       root
    Omar             3                     1                             
    Ali              3                     1                              
    Waheeb           4                     2                            
    Hussin           5                     1                           
    Amal             6                     2                    
    Moien            7                         2     
    Khalid           8                     3                  
    Kamal            9                    root                  
    Gamal            10                   root                  
    Mohamed          11                    5                  
    Mahmoud          12                    2                
    nader            13                    2                 
    Abdullah         14                    1                 
    Rami             15                    4                
    Bogamal          16                    2
    Yes , i thought by DENSE_RANK can but can you help my in write that query code
    many thanks

    Edited by: Ayham on Mar 26, 2013 11:34 AM
  • 3. Re: Group like tree in conceptual view
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    Ayham wrote:
    ... the others was as explanation to help anybody ....
    I need all the help I can get. I don't understand any more than I did before what you want.
    Explain, step by step, how you get belong_group.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points