Given a list of student names and grade. Write a query to print a comma separated list of student names in a grade.

Grade | Name
1 | John
1 | George
1 | Jane
2 | Smith
2 | Anne
2 | Scott
3 | Larry
3 | Bill

We can use LISTAGG function in Oracle for this purpose. It can transpose rows to column type values. We can set the delimiter as comma in LISTAGG function. And then we can group the students by using Grade in GROUP BY clause.

Query will be as follows:

SELECT grade, LISTAGG(name, ‘,’) WITHIN GROUP (ORDER BY name) AS Students
FROM student
GROUP BY grade;

Grade Students

1 John,George,Jane
2 Smith,Anne,Scott
3 Larry,Bill