How to combine rows with same values in mysql

Question

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.

My attempted query is:

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

Why would you want to "put null or space in the rows"? What are you trying to achieve exactly? — rlanvin, Aug 20 '15 at 14:40

score 2 · Accepted Answer · edited May 23 '17 at 12:06

2

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.

edited May 23 '17 at 12:06

Community

1
1

answered Aug 20 '15 at 14:52

Tom Mac

9,693
3
25
35

1

Nice answer, looks like it could work, though I strongly agree with handling that in code rather than in MySQL. – rlanvin Aug 20 '15 at 15:05
extraordinary. this is exactly what I am looking for. Thank you very much. It's a big help. – Peka Chawngthu Aug 20 '15 at 15:50

How to combine rows with same values in mysql

1 Answers1