CODEWITHSUNDEEP
mysqlsqlpivot
Smith
Smith

Reputation: 5961

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

Upvotes: 0

Views: 618

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

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;

Upvotes: 3

Related Questions