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

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