1

Table1 is like this

Id | Name    | Subject
1   |Rakesh  | English
1   |Rakesh  |  Maths
1   |Rakesh  | Science
2   |Mukesh  | English
2   |Mukesh  |  Maths

Table 2 which I want is like this:

Id  | Name   | Subject
1   | Rakesh | English, Maths, Science
2   | Mukesh | English, Maths
cold bloded
  • 45
  • 6

1 Answers1

0

try the following. Here is the Demo

MySQL

 select 
   id, 
   name, 
   group_concat(subject order by subject SEPARATOR ', ') as subject
 from table
 group by
   id,
   name

SQLite - As order by not an option inside group_concat, you can do something like following 

select
    id,
    name,
    group_concat(subject, ', ') as subject
from
(
 select 
   id, 
   name,
   subject
 from student
 order by
    id, subject
)
group by
    id, name
zealous
  • 7,336
  • 4
  • 16
  • 36
  • You need a group by id,name otherwise the aggregate function will be over the whole result set – P.Salmon Apr 17 '20 at 07:23
  • @P.Salmon Thanks. – zealous Apr 17 '20 at 07:25
  • It works but I want to save it in table. with this query it is just displaying in required format and not saving it @zealous – cold bloded Apr 17 '20 at 07:54
  • @coldbloded just do `create table yourTableName` and then put query. this article may be helpful to you https://dev.mysql.com/doc/refman/8.0/en/create-table-select.html – zealous Apr 17 '20 at 07:57