Native Oracle Query to fetch One to Many Relationship Structure

Question

Can any one please help me out for a query to fetch one to many relationship in oracle

Table Structure is:

Table: STUD_NAME                       Table:CLASS                  

STUD_No    STUD_Name                     Class_ID       STUD_No        CLASS_NAME
-------------------                    -------------------------------------------     
1         Sam                            1                1             PHYSICS
2         Michael                        2                1             MATHEMATICS 
3         Patrick                        3                2             PHYSICS
5         Leena                          4                2             CHEMISTRY

I tried using join concepts, hence it returned duplicated rows in STUD_NAME table like

  1         Sam  PHYSICS
  1         Sam  CHEMISTRY

I need an output like

1 Sam  PHYSICS,MATHEMATICS
2 Michael  PHYSICS,CHEMISTRY

Answer 1

CREATE OR REPLACE TYPE t_string_agg AS OBJECT
(
  g_string  VARCHAR2(32767),

  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg)
    RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg,
                                       value  IN      VARCHAR2 )
     RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg,
                                     ctx2  IN      t_string_agg)
    RETURN NUMBER
);
/
SHOW ERRORS


CREATE OR REPLACE TYPE BODY t_string_agg IS
  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg)
    RETURN NUMBER IS
  BEGIN
    sctx := t_string_agg(NULL);
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg,
                                       value  IN      VARCHAR2 )
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := self.g_string || ',' || value;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER IS
  BEGIN
    returnValue := RTRIM(LTRIM(SELF.g_string, ','), ',');
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg,
                                     ctx2  IN      t_string_agg)
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := SELF.g_string || ',' || ctx2.g_string;
    RETURN ODCIConst.Success;
  END;
END;
/
SHOW ERRORS


CREATE OR REPLACE FUNCTION string_agg (p_input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING t_string_agg;
/
SHOW ERRORS

Then execute

select stud_name, string_agg(class_name)
from stud_name s, class c
where s.stud_no = c.stud_no
group by stud_name

Props to asktom.oracle.com

Answer 2

For 9i use:

select   S.STUD_NO, STUD_NAME, WM_CONCAT(CLASS_NAME)
from     STUD_NAME S, CLASS C
where    S.STUD_NO = C.STUD_NO
group by S.STUD_NO, STUD_NAME

For 11g onwards you can use listagg in place of wm_concat. See here for a nice list of string aggregation techniques.

Answer 3

You should be able to do this using the SYS_CONNECT_BY_PATH function. For your example, something like the following should work:

select
   stud_no,
   stud_name,
   substr(SYS_CONNECT_BY_PATH(class_name, ', '),2) classes
from
   (
   select     
     cn.stud_no,
     sn.stud_name,
     cn.class_name,
     count(*) OVER ( partition by cn.stud_no ) cnt,
     ROW_NUMBER () OVER ( partition by cn.stud_no order by cn.class_name) seq
   from
     class_name cn
    ,stud_name sn
   where
     sn.stud_no = cn.stud_no
   group by cn.stud_no, sn.stud_name, cn.class_name)
where
   seq=cnt
start with
   seq=1
connect by prior
   seq+1=seq
and prior
   stud_no=stud_no; 

To break this down...

The inner query

   select     
     sn.stud_no,
     sn.stud_name,
     cn.class_name,
     count(*) OVER ( partition by cn.stud_no ) cnt,
     ROW_NUMBER () OVER ( partition by cn.stud_no order by cn.class_name) seq
   from
     class_name cn
    ,stud_name sn
   where
     sn.stud_no = cn.stud_no
   group by cn.stud_no, sn.stud_name, cn.class_name

will give a result set like this:

    STUD_NO  STUD_NAME  CLASS_NAME  CNT  SEQ
    001      SAM        CHEMISTRY   2    1
    001      SAM        PHYSICS     2    2
    002      MICHAEL    ART         3    1
    002      MICHAEL    HISTORY     3    2
    002      MICHAEL    PHYSICS     3    3

Note that the result set is ordered so that the class records for each student are grouped together according to stud_no. CNT indicates the total number of records in the group (partition) to which the record belongs (by including the total count of all records with the same stud_no), and SEQ indicates a unique sequence/rank within that group (in this case, based on an alphabetic ordering by class name).

The rest of the query then builds a list of comma-delimited class names for each student by traversing the result set using the CNT and SEQ values as follows:

substr(SYS_CONNECT_BY_PATH(class_name, ', '),2) classes
-- builds a list of class names separated by a comma 
-- (the substr function is just there to remove the first delimiter)

where seq=cnt -- this condition indicates the last record in each group/list

start with seq=1 
-- the starting point for each group (i.e. start a new list every time a seq 
-- value of 1 is encountered while traversing the result set)

connect by prior seq+1=seq and prior stud_no=stud_no 
-- defines the connection between one list element and the next; the next 
-- element in a list will have the same stud_no as the prior element AND a 
-- seq equal to the prior element's seq +1 

The result:

    STUD_NO  STUD_NAME  CLASSES
    001      SAM        CHEMISTRY, PHYSICS
    002      MICHAEL    ART, HISTORY, PHYSICS

This approach and a few other possible options are described here: http://www.dba-oracle.com/t_converting_rows_columns.htm

Hope that helps!

Answer 4

Question is formatted badly, but let me try to understand.

Table: STUD_NAME Cols: STUD_No, STUD_Name

Table: CLASS Cols: Class_ID, STUD_No, CLASS_NAME

Query should return each student and the class that they are taking.

Here is an example I did in SQLite... tell me what is wrong...

sqlite> create table stud_name (stud_no integer, stud_name text);
sqlite> create table class (class_id integer, stud_no integer, class_name text);
sqlite> insert into stud_name values (1, 'Sam');
sqlite> insert into stud_name values (2, 'Mike');
sqlite> insert into stud_name values (3, 'Pat');
sqlite> insert into stud_name values (4, 'Leena');
sqlite> insert into class values (1,1,'Physics');
sqlite> insert into class values (2,1,'Math');
sqlite> insert into class values (3,2,'Physics');
sqlite> select stud_name, class_name from stud_name s, class c
   ...> where s.stud_no = c.stud_no;
Sam|Math
Sam|Physics
Mike|Physics

Answer 5

Ok, in SQLite I was able to accomplish by...

sqlite> select stud_name, group_concat(class_name)
   ...> from stud_name s, class c
   ...> where s.stud_no = c.stud_no
   ...> group by stud_name
   ...> ;
Mike|Physics
Sam|Math,Physics

In Oracle 9i, I believe you can just replace group_concat from above with wm_concat.

Let me know!