How to Join three tables with multiple values rows returned on the other two

Question

Say for example.

I have three separate tables:

1. course Table which has CourseId, StudentIds etc
2. student which of course contains student data and StudentName
3. score table

I only want one column from each table and fuse them into one.

CourseId        StudentName            Scores
----------      -------------          ----------
1               Gashio                 10
1               Gashio                 20
1               Lee                    35
1               Lee                    40
1               Edith                  5
2               Lana                   3
2               Reisha                 50

For every Course there's multiple students, and for every Scores there's multiple scores they get from the course for a month.

I wanted something like this as a result:

CourseId        StudentName           Scores
---------       -------------         -------------
1               Gashio                10|20 
1               Lee                   35|40
1               Edith                 5
2               Lana                  3
2               Reisha                50

Since the scores return multiple values, I wanted it to become one column separated by a delimeter.

I'm not sure if this is where I should be using STRING_AGG?

Answer 1

You need STRING_AGG and GROUP BY

SELECT course.CourseId, 
       student.StudentName, 
       STRING_AGG(Scores, ,'|') AS Scores
  FROM course INNER JOIN
       student ON student.StudentId  = course.StudentId INNER JOIN
       score ON score.studentId = student.StudentId
 GROUP BY cource.CourseId, 
          student.StudentName