How to join two tables that has one to many relationship in bigquery

Question

Let say I have student Table like below in BigQuery:

Mark Table looks like below in Big Query:

I would like to join this two tables and create a new table like below :

Answer 1

Consider below query,

SELECT s.*, m.Mark_Details
  FROM student_table s
  JOIN (
    SELECT student_ID,
           STRING_AGG('' || Subject_ID || ':' || Subject_Name || ':' || Mark, ' | ') AS Mark_Details
      FROM mark_table
     GROUP BY 1
  ) m USING (student_ID)