SQL - How do i get multiple values from another table to fit in a single row in a select statement?

I'm sure there's a simple answer to this but i just spent the last 3 hours searching google to no avail. So, we have two three tables - courses, students, and courses_students

courses_students contain开发者_如何学运维s the primary keys of courses and students and is there to break up the m:m relationship.

My homework wants me to write a query to show all the details of a particular course.. including a list of all students in that course. I tried using every type of join possible but ended up getting multiple rows with course information.

i.e. it should show the details for the course once, and include all students e.g.

courseid     coursename       student
------------ ---------------- ---------------
1            math             john jackson
                              jack johnson
                              john smith
2            english          jane doe
                              michael thomas

etc... Please help!

thanks!

p.s. i'm using oracle

---

SQL doesn't really deal in hierarchical data, it deals in sets. This is something better handled in 2 queries - one that returns the course information, and one that returns the students in the course.

---

look up user defined aggregate functions. if you really need to list them all in one column, you can set up an aggregate function, and it will do that for you.

Declare
   sql_txt      Varchar2(4000);
   Rec_cnt      Number;
Begin
   Select Count(*)
     Into Rec_Cnt
     From User_Types
    Where Type_Name = 'VCARRAY'
      And Typecode = 'COLLECTION';

  If Rec_Cnt = 0 Then
     EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE vcArray as table of varchar2(32000)';
  END IF;
END;  
/

CREATE OR REPLACE TYPE comma_list_agr_type as object
  (
     data  vcArray,

     static function
          ODCIAggregateInitialize(sctx IN OUT comma_list_agr_type )
          return number,

     member function
          ODCIAggregateIterate(self IN OUT comma_list_agr_type ,
                               value IN varchar2 )
          return number,

     member function
          ODCIAggregateTerminate(self IN comma_list_agr_type,
                                 returnValue OUT  varchar2,
                                 flags IN number)
          return number,

     member function
          ODCIAggregateMerge(self IN OUT comma_list_agr_type,
                             ctx2 IN comma_list_agr_type)
          return number
  );
/


CREATE OR REPLACE TYPE BODY comma_list_agr_type
  is

  static function ODCIAggregateInitialize(sctx IN OUT comma_list_agr_type)
  return number
  is
  begin
      sctx := comma_list_agr_type( vcArray() );
      return ODCIConst.Success;
  end;

  member function ODCIAggregateIterate(self IN OUT comma_list_agr_type,
                                       value IN varchar2 )
  return number
  is
  begin
      data.extend;
      data(data.count) := value;
      return ODCIConst.Success;
  end;

  member function ODCIAggregateTerminate(self IN comma_list_agr_type,
                                         returnValue OUT varchar2,
                                         flags IN number)
  return number
  is
      l_data varchar2(32000);
  begin
      for x in ( select column_value from TABLE(data) order by 1 )
      loop
              l_data := l_data || ',' || x.column_value;
      end loop;
      returnValue := ltrim(l_data,',');
      return ODCIConst.Success;
  end;

  member function ODCIAggregateMerge(self IN OUT comma_list_agr_type,
                                     ctx2 IN comma_list_agr_type)
  return number
  is
  begin -- not really tested ;)
      for i in 1 .. ctx2.data.count
      loop
              data.extend;
              data(data.count) := ctx2.data(i);
      end loop;
      return ODCIConst.Success;
  end;
  end;
/

CREATE OR REPLACE FUNCTION comma_list(input varchar2 )
  RETURN varchar2
  PARALLEL_ENABLE AGGREGATE USING comma_list_agr_type;
/

GRANT EXECUTE ON COMMA_LIST to someuser
/

---

There are several different ways of approaching this. The simplest is presentational: solve it in the front end display. In SQL*Plus that would be the BREAK keyword:

SQL> BREAK ON courseid ON coursename
SQL>
SQL> select c.courseid
  2         , c.coursename
  3         , s.studentname
  4  from  courses c
  5          join course_students cs
  6              on ( cs.courseid = c.courseid )
  7          join students s
  8              on ( s.studentid = cs.studentid )
  9  /

  COURSEID COURSENAME STUDENTNAME
---------- ---------- --------------------
         1 math       john smith
                      jack jackson
                      john jackson
         2 english    michael thomas
                      jane doe

SQL>

Another approach is to use an embedded cursor:

SQL> select c.courseid
  2         , c.coursename
  3         , cursor (select  s.studentname
  4                   from course_students cs
  5                  join students s
  6                      on ( s.studentid = cs.studentid )
  7                  where cs.courseid = c.courseid
  8                  )
  9  from  courses c
 10  /

  COURSEID COURSENAME CURSOR(SELECTS.STUDE
---------- ---------- --------------------
         1 math       CURSOR STATEMENT : 3

CURSOR STATEMENT : 3

STUDENTNAME
--------------------
john smith
john jackson
jack jackson

         2 english    CURSOR STATEMENT : 3

CURSOR STATEMENT : 3

STUDENTNAME
--------------------
jane doe
michael thomas


SQL>

We can debate whether that truly counts as "a single row" :)

Finally we have string aggregation techniques. There are a number of different ways of slicing this particular cabbage, because - unbelievably - it wasn't until the very latest release that Oracle provided a standard built-in to do it. Because I'm not on 11gR2 I'll use WM_CONCAT() instead of LISTAGG():

SQL> select c.courseid
  2         , c.coursename
  3         , wm_concat(s.studentname) as studentnames
  4  from  courses c
  5          join course_students cs
  6              on ( cs.courseid = c.courseid )
  7          join students s
  8              on ( s.studentid = cs.studentid )
  9  group by c.courseid
 10             , c.coursename
 11  /

  COURSEID COURSENAME STUDENTNAMES
---------- ---------- ---------------------------------------------
         1 math       john smith,john jackson,jack jackson
         2 english    jane doe,michael thomas

SQL>

Tim Hall's Oracle-Base site has a round up of all of the string aggreation options. Find out more.

---

if you simply need the results in a query why not this?

with courses as
(select 'biology' coursename, 1 courseid from dual
union
 select 'chemistry' coursename, 2 courseid from dual)
 ,
 students as
 (select 'Sally' studentName, 1 studentId from dual
  union
  select 'Jonny' studentName, 2 studentId from dual
  union
  select 'Tom' studentName, 3 studentId from dual
  union
  select 'Jane' studentName, 4 studentId from dual
  ) ,
  courses_students as
  (select 1 studentId, 1 courseId from dual
    union
    select 1 studentId, 2 courseId from dual
    union
    select 2 studentId, 1 courseId from dual
    union
    select 3 studentId, 2 courseId from dual
  )
  select c.courseName ,
         cursor(select s.StudentName
                        from students s
                             inner join
                              courses_students cs
                                on s.studentId = cs.studentId
                      where cs.courseId = c.courseId) students
        from courses c ;

granted there are no types but this'll work.

COURSENAME STUDENTS 
---------- -------- 
biology    STUDENTNAME  
           -----------  
           Sally        
           Jonny        


chemistry  STUDENTNAME  
           -----------  
           Sally        
           Tom          

all in one query and literally doing nothing too fancy (just using the CURSOR statement)

if you are using 11gr2 ListAgg works great

---

Which version of Oracle you are in? By chance if you are using Oracle DB 11g R2, take look at listagg.

SQL> select deptno,
  2         listagg( ename, '; ' )
  3         within group
  4         (order by ename) enames
  5     from hr.employees
  6    group by deptno
  7    order by deptno
  8   /

  DEPTNO   ENAMES
---------  --------------------
  10       CLARK; KING; MILLER
  20       ADAMS; FORD; JONES;
           SCOTT; SMITH
  30       ALLEN; BLAKE;
           JAMES; MARTIN;
           TURNER; WARD

Your case you need to do it for course table. In prior versions you can do with CONNECT BY clause. More details on listagg.

---

sql suck (really..???!!),

The first thing you should clarify is whether the intention of the question is to present the data in that format (or) the query itself should show the data in this format.

a) if it is just the presentation, take a look at SQLPLUS "break on". What this allows you to do is break on a particular column and not repeat the same values if the value hasn't changed.

b) 1) If it is the query that should output data in this format, then look at the approach suggested by "tanging" above. if you want to explore more options, 2) see the lead and lag functions. you can see the value of a column in the previous row using these functions. If they are the same , you'll display null, otherwise the actual value.

Imp : For options a) and b(2) , you should order the results using the order by clause. (Why..?)

Also, check this link : How do I remove repeated column values from report