Originally posted by mike9
You can not do this with a sql query. You have to use PLSQL
Oh no no no, my good man. You'd be amazed what can be done in SQL.

Try this:
Code:
CREATE TABLE CRL_STUDENT_COURSE (STUDENT_ID NUMBER, COURSE_CD CHAR(4));


INSERT INTO CRL_STUDENT_COURSE VALUES(1, 'F101');
INSERT INTO CRL_STUDENT_COURSE VALUES(1, 'D106');
INSERT INTO CRL_STUDENT_COURSE VALUES(2, 'S105');
INSERT INTO CRL_STUDENT_COURSE VALUES(2, 'C101');
INSERT INTO CRL_STUDENT_COURSE VALUES(3, 'C101');
INSERT INTO CRL_STUDENT_COURSE VALUES(3, 'A503');
INSERT INTO CRL_STUDENT_COURSE VALUES(5, 'C102');

SELECT
   T.STUDENT_ID,
   MAX(DECODE(T.RN, 1,       COURSE_CD)) ||
   MAX(DECODE(T.RN, 2,', '|| COURSE_CD)) ||
   MAX(DECODE(T.RN, 3,', '|| COURSE_CD)) ||
   MAX(DECODE(T.RN, 4,', '|| COURSE_CD)) ||
   MAX(DECODE(T.RN, 5,', '|| COURSE_CD)) ||
   MAX(DECODE(T.RN, 6,', '|| COURSE_CD)) ||
   MAX(DECODE(T.RN, 7,', '|| COURSE_CD)) ||
   MAX(DECODE(T.RN, 8,', '|| COURSE_CD)) ||
   MAX(DECODE(T.RN, 9,', '|| COURSE_CD)) ||
   MAX(DECODE(T.RN,10,', '|| COURSE_CD))
      AS COURSE_CD_LST   
FROM
   (
   SELECT
      CSC.*   ,
      ROW_NUMBER()
         OVER(
            PARTITION BY 
               CSC.STUDENT_ID
            ORDER BY
               CSC.COURSE_CD
            )
         AS   RN
   FROM
      CRL_STUDENT_COURSE   CSC
   )   T
GROUP BY
   T.STUDENT_ID
The main problem is that the number of courses in the list is arbitrary. However, it really doesn't hurt much if you want to expand the code to handle 500 courses. It'll most likely still be faster than a PL/SQL solution.

- Chris