AYRIL AYRIL - 27 days ago 11
MySQL Question

My Sql Data output convert rows to column like pivot table

I have a table called marks. sample data from my table is given below. In my table the subject field is dynamic, name and number of subject may vary based on the class.

ID Name Subject Marks
------ --------- ---------- ---------
1001 John Maths 78
1001 John English 88
1001 John Computer 92
1002 Mary Maths 81
1002 Mary English 85
1002 Mary Computer 90


How can I get an out put of the above table data in below format. I don't know whether this is a simple question, i am beginner in SQL and developing field. Please help.

ID Name Maths English Computer
---- -------- --------- --------- ------------
1001 John 78 88 92
1002 Mary 81 85 90

Answer

A simpler solution is to use conditional aggregation like so

Select id,name,
sum(case when s.SUBJECT='Computer' then s.marks else 0 end) as Computer,
sum(case when s.SUBJECT='English' then s.marks else 0 end) as English,
sum(case when s.SUBJECT='Maths' then s.marks else 0 end) as Maths 
from marks s group by id,name;

If you don't know how many subjects there are then you have to create a sql statement and run it (dynamic sql). I do this in easy stages so

set @sumstr = 
(select str from
(
select @rn:=@rn + 1 rn,@str:=concat(@str,'sum(case when s.SUBJECT=',char(39),s.SUBJECT,char(39),' then s.marks else 0 end) as ', s.SUBJECT, ',' ) str
from (select @rn:=0,@str:='') str,(SELECT DISTINCT SUBJECT FROM MARKS) s
ORDER BY S.SUBJECT
) s
order by rn desc limit 1
);

creates the sum statements which I then enrich like so

SET @SQLSTR = concat(
                'Select id,name,'
                ,SUBSTRING(@SUMSTR,1,LENGTH(@SUMSTR) -1)
                ,' from marks s group by id,name;'
                )
;

to create the sql statement at the top of this solution. I then prepare and execute it

prepare dynamic_statement from @sqlstr;
execute dynamic_statement;
Comments