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

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

Answer:
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

Read the full book at www.amazon.com
Posted in SQL, SQL Interview Questions

Leave a Reply

Your email address will not be published. Required fields are marked *

*