Remove null values in MySQL and combine non null values into one cell

Question

Hello guys? I have a MySQL table storing timetable classes, a specific subject is allocated to a specific timeslot stored in columns. I have no issue when there are no two subjects taught at the same time since I use max() to select from the time table i.e

Select dayid,max(`1`) as `1` ,
 max(`2`) as `2` ,
 max(`4`) as `4` ,
 max(`5`) as `5` ,
 max(`7`) as `7` ,
 max(`8`) as `8` ,
 max(`10`) as `10` ,
 max(`11`) as `11` ,
 max(`13`) as `13` ,
 max(`14`) as `14` 
 FROM  dd GROUP BY dd.days ORDER BY dayid

The above query gives me this results

Now from the first image you can see that the highlighted rows have the same dayid and same timeslot which means they are taught at the same time but my query will only give me one subject (KISW F1A)

I want to achieve this (KISW F1A/BIO F1A) in the cells where classes are taught at the same time.

Most people here want sample table data and expected result as formatted text, i.e. not as images. — jarlh
– jarlh, Commented Oct 21, 2020 at 7:49
@jarlh thanks for the heads up, I thought the image is well presented with an exact problem I was dealing with. — Benson Kiprono
– Benson Kiprono, Commented Oct 21, 2020 at 8:56

Fahmi · Accepted Answer · 2020-10-21 07:27:12Z

1

Use group_concat()

Select dayid,group_concat(`1`) as `1` ,
 group_concat(`2`) as `2` ,
 group_concat(`4`) as `4` ,
 group_concat(`5`) as `5` ,
 group_concat(`7`) as `7` ,
 group_concat(`8`) as `8` ,
 group_concat(`10`) as `10` ,
 group_concat(`11`) as `11` ,
 group_concat(`13`) as `13` ,
 group_concat(`14`) as `14` 
 FROM  dd GROUP BY dd.days ORDER BY dayid

answered Oct 21, 2020 at 7:27

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

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

Collectives™ on Stack Overflow

Remove null values in MySQL and combine non null values into one cell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related