1

Here's the table and what if I want to aggregate the courseID for each student. Can I do that in SQL?

StudentCourse
-------------
StudentID  CourseID
1          101
1          102
1          103
2          103

What I want:

StudentCourse
-------------
StudentID  CourseID
1          101,102,103
2          103
Jack
  • 67
  • 3
  • You want this as output of a query, or you want to create a new table that has that format? – mypetlion May 03 '18 at 15:39
  • Are you using sql server? – Daniel Marcus May 03 '18 at 15:39
  • Loop through each row and build a variable as you loop that contains all the courseids per student - then write that value to a new table and select from there when you are done – Daniel Marcus May 03 '18 at 15:41
  • 2
    Which [DBMS](https://en.wikipedia.org/wiki/DBMS) are you using? "SQL" is just a query language, not the name of a specific database product. Please add the tag for the database product you are using `postgresql`, `oracle`, `db2`, `sql-server`, ... –  May 03 '18 at 15:47
  • For Postgres: `select studentid, string_agg(courseid, ',') as courseid from studentcourse group by studentid` –  May 03 '18 at 15:48
  • Possible duplicate of [Optimal way to concatenate/aggregate strings](https://stackoverflow.com/questions/13639262/optimal-way-to-concatenate-aggregate-strings) – AHiggins May 03 '18 at 16:21

1 Answers1

2

If you are using Oracle database then you can use "LISTAGG" function.

SELECT StudentID,
    LISTAGG(CourseID, ', ') WITHIN GROUP (ORDER BY StudentID) as CourseID
      FROM StudentCourse
        GROUP BY StudentID;
SwapnaSubham Das
  • 515
  • 5
  • 23