mysql select dynamic rows as column names

Question

Student_id | Skill_id  | skill-Score
1              30            5
1              50            1
1              63            2
1              42            2

2              30            7
2              50            3
2              63            6
2              42            9

I am trying to create a table in this format how can i do this.?

Student_id ||    30   ||     50   ||     63   ||    42   |
    1             5          1           2           2
    2             7          3           6           9

here what i have tried but no result please do not consider my try.

SELECT CONCAT(
  'SELECT `r_job_scores`.id_score', GROUP_CONCAT('
     ,    `t_', REPLACE(skill_id, '`', '``'), '`.value
         AS `', REPLACE(skill_id, '`', '``'), '`'
     SEPARATOR ''),
 ' FROM `r_job_scores` ', GROUP_CONCAT('
     LEFT JOIN `r_job_scores`   AS `t_', REPLACE(skill_id, '`', '``'), '`
            ON `r_job_scores`.id_score = `t_', REPLACE(skill_id, '`', '``'), '`.id_score
           AND `t_', REPLACE(skill_id, '`', '``'), '`.skill_id = ', QUOTE(skill_id)
     SEPARATOR ''),
 ' GROUP BY `r_job_scores`.id_score'
)

Answer 1

If the number of skill IDs are known and fixed, then you can use a simple pivot query:

SELECT Student_Id,
       MAX(CASE WHEN Skill_id = 30 THEN skill-Score END) AS `30`,
       MAX(CASE WHEN Skill_id = 50 THEN skill-Score END) AS `50`,
       MAX(CASE WHEN Skill_id = 63 THEN skill-Score END) AS `63`,
       MAX(CASE WHEN Skill_id = 42 THEN skill-Score END) AS `42`
FROM r_job_scores
GROUP BY Student_Id

If you want to get a total of student scores, one way to do that would be to subquery the query I gave above and compute the tally:

SELECT t.Student_Id,
       t.`30` + t.`50` + t.`63` + t.`42` AS overall_score
FROM
(
    SELECT Student_Id,
           MAX(CASE WHEN Skill_id = 30 THEN skill-Score END) AS `30`,
           MAX(CASE WHEN Skill_id = 50 THEN skill-Score END) AS `50`,
           MAX(CASE WHEN Skill_id = 63 THEN skill-Score END) AS `63`,
           MAX(CASE WHEN Skill_id = 42 THEN skill-Score END) AS `42`
    FROM r_job_scores
    GROUP BY Student_Id
) t

Answer 2

You can achieve this with dynamic sql.

Query

set @query = null;
select
  group_concat(distinct
    concat(
      'max(case when `Skill_id` = ',
      `Skill_id`, ' then `skill-Score` end) as `', `Skill_id` , '`'
    )
  ) into @query
from `your_table_name`;

set @query = concat('select `Student_id`, ', @query, ' from `your_table_name` 
              group by `Student_id`
');

prepare stmt from @query;
execute stmt;
deallocate prepare stmt;

Output

+------------+----+----+----+----+
| Student_id | 30 | 50 | 63 | 42 |
+------------+----+----+----+----+
| 1          | 5  | 1  | 2  | 2  |
| 2          | 7  | 3  | 6  | 9  |
+------------+----+----+----+----+

SQL Fiddle demo