Click here to Skip to main content
14,694,330 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
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
Posted
Updated 19-Mar-20 22:29pm
v2

1 solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900