My Sql Data output convert rows to column like pivot table

Question

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

If you google your question title you will find loads of stuff on this topic. — P.Salmon
– P.Salmon, Commented Nov 8, 2016 at 12:53
Yes, but i am not able to find any straight forward solution (may by my lack of knowldge) for my requirement. Because of the dynamic 'subject' field its little bit confusing. other wise i can handle. The below answers also fine in case the 'subject' field is not dynamic. — AYRIL
– AYRIL, Commented Nov 8, 2016 at 15:23

P.Salmon · Accepted Answer · 2016-11-08 15:54:06Z

1

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;

answered Nov 8, 2016 at 15:54

P.Salmon

17.7k2 gold badges14 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

AYRIL Over a year ago

Wow!!! this is working as expected. But unfortunately, i am not able to understanding the prepared query, because i have not that much knowledge in SQL.

AYRIL Over a year ago

If you don't mind, could you please explain/elaborate the answer provided by you( the 2nd answer). Which is working as expected by me.

P.Salmon Over a year ago

In the set sumstr statement the The sub query s builds a 3 row temporary table (1 row for each distinct subject) containing a row number (rn) and a string (which gets longer for each iteration). and rn is incremented by 1. We are interested in the last entry in the temporary table and we get that using the order by ... limit clause. The Set SQLSTR completes the statement we wish to submit to sql. The prepare and execute statements are how we submit to sql,

AYRIL Over a year ago

@Salmon Is it possible to execute the above shared query directly to PHP. I have tried, but unfortunately i couldn't execute it in my php page.

P.Salmon Over a year ago

Sorry- I don't know php

Viki888 · Accepted Answer · 2016-11-08 12:56:01Z

0

You can make use of below query,

SELECT ID, 
       Name,
       CAST(SUBSTRING_INDEX(Marks, ',', 1)  AS UNSIGNED) Maths, 
       CAST(SUBSTRING_INDEX(Marks, ',', 2) AS UNSIGNED) English, 
       CAST(SUBSTRING_INDEX(Marks, ',', 3) AS UNSIGNED) Computer 
FROM  (SELECT ID, 
              Name,
              GROUP_CONCAT(CAST(Marks AS VARCHAR(100))) Marks
       FROM marks
       GROUP  BY ID, 
              Name) derived_table2

Hope this should solve your problem.

answered Nov 8, 2016 at 12:56

Viki888

2,7742 gold badges15 silver badges16 bronze badges

1 Comment

P.Salmon Over a year ago

@vikki888 Solution does not syntax (varchar(100)) and when "fixed" does not produce the expected result

Sukhvindra Singh · Accepted Answer · 2016-11-08 13:39:00Z

0

you can also try like..


select id,name,sum(Maths)Maths,sum(English)English,sum(computer)computer
from(
select id,name,
case when Subject='Maths' then Marks else 0 end Maths,
case when Subject='English' then Marks else 0 end English,
case when Subject='computer' then Marks else 0 end computer
from(
select * from your_table_name
)A
 )B group by id

answered Nov 8, 2016 at 13:39

Sukhvindra Singh

2023 silver badges14 bronze badges

Collectives™ on Stack Overflow

My Sql Data output convert rows to column like pivot table

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related