I'm new to ORACLE and I'm trying to use a string aggregation function in my query to concatenate the value of rows with a comma ,

this is the structure of my tables:

  1. Student(STUDENT_ID,student_name,age)
  2. Course(course_no,description)
  3. Student_Course(student_id,course_id,nomreh)

description column indicates the name of the course for example Algebra or Math. Nomreh column shows the Mark each student achieved in each course (it's a Persian word).

This is my query

Select s.student_name , 
   LISTAGG(c.description, ',') WITHIN GROUP (ORDER BY c.description) AS 
   LISTAGG(sc.nomreh, ',') WITHIN GROUP (ORDER BY sc.nomreh) AS Ranks
from student s inner join 
        student_course sc on s.student_id = sc.student_id 
           inner join
        course c on sc.course_id = c.course_no
 group by s.student_name 

I want the output to show:

[Student-name]   [Course-Description]    [Nomre]
  Artin           Algebra,Math,Sport    10,11,12

each value in [Nomreh] column should be exactly for that [Course] column and for that specific student . Unfortunately my query does not give the desired output and the values in Nomreh column are not in correct order with [Course-Description] column.


Change this part of your query to the below:

       LISTAGG(c.description, ',') WITHIN GROUP (ORDER BY 
                s.student_name,c.description,sc.nomreh) AS Courses,
       LISTAGG(sc.nomreh, ',') WITHIN GROUP (ORDER BY 
                s.student_name,c.description,sc.nomreh) AS Ranks

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.