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
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.
