Transposing data from multiple rows into a single row multiple columns using DYNAMIC MYSQL PIVOT

See more:

MySQL

MySQL-Connector

I have a table structure similar to this

CREATE TABLE score (
id int,
reg_id varchar(5),
test1 int(5),
exam int(5),
subject int(5),
term VARCHAR(5)
)
<pre>CREATE TABLE subject (
id int,
 subject_name(5)
)

I want to be able to select more than one value e.g test1 and exam into different column in the DYNAMIC MYSQL PIVOT TABLE. I have seen similar but they seems to be selecting only 1 matching value into the dynamic pivot table. I have tried writing something like this

What I have tried:

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(case when subject = ''',
      subject,
      ''' then test1 end)AS `',
      (select subject_name from subject where id=subject), '`',

        'SUM(case when subject = ''',
      subject,
      ''' then exam end)AS `',
      (select subject_name from subject where id=subject), '`'
    )
  ) INTO @sql
FROM
  scores;
SET @sql = CONCAT('SELECT reg_id, ', @sql, ' 
                  FROM scores  where term="First term"
                   GROUP BY reg_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Any help will be appreciated