Smith Smith - 5 months ago 15
SQL Question

pivot rows to columns based on condition

I have a table like so

id student_id score
1 1 45
2 2 55
3 2 75
4 3 80
5 1 90
6 2 78
7 3 55
8 1 45
9 1 65


I want to arrange it like this

student_id s1 s2 s3 s4
1 45 90 45 65
2 55 75 78 -
3 80 55 - -


the concept of pivot is

SELECT
item_id,
MAX(IF(property_name = 'property_value1', value, NULL)) AS alias1,
MAX(IF(property_name = 'property_value2', value, NULL)) AS alias2,
...
...
...
FROM
table
GROUP BY
item_id;


which i cannot really figure out in my case, as i am creating the columns s1 - s4 by occurrence, i.e the first score for each student becomes s1, second becomes s2 etc.

how do i solve this

Answer

The simplest method is to put the values in a single column:

select student_id, group_concat(score order by id)
from t
group by student_id;

That is sufficient for many purposes. If you want separate columns, you need to create a column. One way uses variables:

select student_id,
       max(case when rn = 1 then score end) as score_1,
       max(case when rn = 2 then score end) as score_2,
       max(case when rn = 3 then score end) as score_3,
       max(case when rn = 4 then score end) as score_4
from (select t.*,
             (@rn := if(@s = student_id, @rn + 1,
                        if(@s := student_id, 1, 1)
                       )
             ) as rn
      from t cross join
           (select @s := -1, @rn := 0) params
      order by student_id, id
     ) t
group by student_id;
Comments