convert mysql query result rows to columns

Question

I have a mysql table called gps_stdnt_subj_xref, sample data and table format is given below.

gps_subj_id  gps_subject    gps_class   gps_subject_status

3            English        class_01    Y
10           EVS            class_01    Y
1            Maths          class_01    Y
11           EVS            class_02    Y
7            Maths          class_02    Y
12           EVS            class_03    Y
2            Maths          class_03    Y
13           EVS            class_04    Y
8            Maths          class_04    Y
5            English        class_05    Y
14           EVS            class_05    Y
9            Maths          class_05    Y
6            English        class_07    Y
4            English        class_12    Y

is it possible to get the table data in to the below format?

class           subject      subject    subject

class_01        English      EVS        Maths
class_02        Evs          Maths
class_03        EVS          Maths
clsss_12        English

lets check this ;http://stackoverflow.com/questions/13451605/how-to-use-group-concat-in-a-concat-in-mysql — newuserua_ext, Oct 13 '16 at 07:54

score 0 · Answer 1 · answered Oct 13 '16 at 07:58

Below is the closest way i could find to your requirement:

    SELECT   gps_class,
        GROUP_CONCAT(IF( gps_subject='English',gps_subject,NULL))
        AS `eng_subject`,
        GROUP_CONCAT(IF(gps_subject='EVS' ,gps_subject,NULL))
        AS `evs_subject`,
        GROUP_CONCAT(IF(gps_subject='Maths',gps_subject,NULL))
        AS `maths_subject`
    FROM     gps_stdnt_subj_xref
    GROUP BY gps_class

convert mysql query result rows to columns

1 Answers1