Combine multiple rows data into same row and different columns : mysql

Question

Suppose you have following base table :

student_id  student_name  course_name   subscribed 
--------------------------------------------------
    001       vishnu        english      YES
    001       vishnu        arabic       NO
    001       vishnu        chinese      YES

I need it in the following format :

student_id  student_name     english   arabic  chinese
------------------------------------------------------
    001     vishnu             YES       NO     YES

I need the mysql query for this. Please help or guide me in the right direction.

Consider handling issues of data display in application code — Strawberry
– Strawberry, Commented Feb 7, 2019 at 8:29

Fahmi · Accepted Answer · 2019-02-07 04:38:21Z

1

You can try using conditional aggregation

select student_id, student_name,
       max(case when course_name='english' then subscribed end) as english,
       max(case when course_name='arabic' then subscribed end) as arabic,
       max(case when course_name='chinese' then subscribed end) as chinese
from tablename group by student_id, student_name

answered Feb 7, 2019 at 4:38

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

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

Comments

Vijiy · Accepted Answer · 2019-02-07 04:34:13Z

0

You have to use case with group by.

select
student_id,
student_name,
max(case when course_name='english' then subscribed else 'A' end) as english,
max(case when course_name='arabic' then subscribed else 'A' end) as arabic,
max(case when course_name='chinese' then subscribed else 'A' end) as chinese
from table group by student_id, student_name;

answered Feb 7, 2019 at 4:34

Vijiy

1,1977 silver badges23 bronze badges

Comments

Jorge Lopez · Accepted Answer · 2019-02-07 05:28:48Z

0

I would pivot that: (SQL Server Solution)

create table #temp (studentId int, studentName nvarchar(75), course_name nvarchar(75),subscribed nvarchar(3))


insert into #temp values (1,'James','English','Yes')
insert into #temp values (2,'Victor','Arabic','No')
insert into #temp values (2,'Victor','Chinese','Yes')
insert into #temp values (2,'William','Chinese','Yes')

select * from (select * from #temp) t1 pivot(max(subscribed) for course_name in ([English],[Arabic],[Chinese]))t1

studentId   studentName English Arabic  Chinese
    1         James      Yes    NULL    NULL
    2         Victor     NULL   No      Yes
    2         William    NULL   NULL    Yes

edited Feb 7, 2019 at 5:28

answered Feb 7, 2019 at 4:56

Jorge Lopez

4771 gold badge4 silver badges10 bronze badges

3 Comments

Vishnu Over a year ago

I am getting this error ??

10:46:47	select * from (select * from student_temp) t1 pivot(max(subscribed) for course_name in ([English],[Arabic],[Chinese]))t1	Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'pivot(max(subscribed) for course_name in ([English],[Arabic],[Chinese]))t1' at line 1	0.051 sec

. @Jorge Lopez

Jorge Lopez Over a year ago

it appears my solution is meant for SQL server, not mySQL. Sorry about that, Vishnu. I just realized the scope of your question.

Jorge Lopez Over a year ago

I would delete the comment, but mySQL results appear when searching for SQL server. So, it might be useful for other users.

Pascal Tovohery · Accepted Answer · 2019-02-07 07:29:30Z

It should be like this, this request work only if there's no duplicate data, like duplicate english course just for one student.

SELECT 
  std.student_id,
  std.student_name,
  (SELECT 
     (CASE WHEN t1.cours_name = 'english' THEN "YES" ELSE "NO" END) 
   FROM table as t1 
   WHERE 
      t1.student_id = std.student_id 
      AND 
      t1.cours_name = 'english') AS english,
  (SELECT 
     (CASE WHEN t1.cours_name = 'arabic' THEN "YES" ELSE "NO" END) 
   FROM table as t1 
   WHERE 
      t1.student_id = std.student_id 
      AND 
      t1.cours_name = 'arabic') AS arabic,
  (SELECT 
     (CASE WHEN t1.cours_name = 'chinese' THEN "YES" ELSE "NO" END) 
   FROM table as t1 
   WHERE 
      t1.student_id = std.student_id 
      AND
      t1.cours_name = 'chinese') AS chinese
FROM 
  table as std
GROUP BY std.student_id;

Collectives™ on Stack Overflow

Combine multiple rows data into same row and different columns : mysql

4 Answers 4

Comments

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related