Search code examples
mysqlgroup-bygroup-concat

How to combine rows with same values in mysql

How do I combine rows with same values in the first row and put null or space in the rows instead without affecting GROUP BY subject in the select statement? Have a look at what I am trying to achieve and help me. enter image description here

My attempted query is:

SELECT regd, GROUP_CONCAT(name order by name SEPARATOR ' ') as name,
 subject, sc, fm FROM table GROUP BY regd, subject
Solution

  • Firstly, I would suggest that you handle this in code rather than at the DB level!

    But, if you absolutely must do it all in a query, you could try ranking over partition with the regd column being the partition. Your expected output has rather arbitrarily ordered rows within each regd.

    This query will order by subject within each regd:

    select t.regd,
case when r=1 then t.name else null end as name,
t.subject,
t.sc,t.fm
from
(
 select tt.*,
 case when regd = @curRegd then @rank := @rank +1 else @rank:=1 end as r,
 @curRegd := tt.regd
 from table tt
 join (SELECT @curRegd := 0,@rank:=0) r
 order by regd,subject
) t

    Finally, based on your stored data example, it seems like no aggregation i.e. GROUP BY clause, is necessary here.