Carl Carl - 5 months ago 8
SQL Question

Oracle Turn 1 to many relationship into single rows

I have 2 Oracle (Oracle 12) tables

StudentTable
s_id number
s_name varchar

Data in Table --> 1 Joe


ClassTable
c_student_id number
c_className varchar

Data in table --> 1 Math
1 History
1 Sceince


I want to write a query that will take the 3 rows and turn it into 1 comma delimited column.

I want my output to look like the following:

Joe Math, History, Science


Is there a way to create this query?

MT0 MT0
Answer
SELECT MAX( s_name ) KEEP ( DENSE_RANK FIRST ORDER BY s.s_id ) AS name,
       LISTAGG( c_class_name, ', ' ) WITHIN GROUP ( ORDER BY ROWNUM ) AS classes
FROM   Students s
       INNER JOIN
       Classes c
       ON ( s.s_id = c.c_student_id )
GROUP BY s.s_id;
Comments