group_concat and NULL in mysql
Well, today I spent 2 hours on a query that involved group_concat.
we had a table that looked like this:
Columns:
……….
client_name
phone
mobile
email
……….
and in a complex query we needed to have group_concat(client_name), group_concat(phone), group_concat(mobile), group_concat(email) …
[note: actually we did : cast(group_concat(client_name) as char), cast(group_concat(phone) as char), cast(group_concat(mobile) as char), cast(group_concat(email) as char)... ]
the thing is that phone and mobile were missing for most of the clients, and the value was NULL.
now if you have for phone values:
NULL
123456789
NULL
NULL
then group_concat(phone) is 123456789
and i woud get:
clients phone
john,george,alex,vio 123456789
and i wouldn’t know which client had that phone.
what i needed was somethin like:
clients phone
john,george,alex,vio _,123456789,_,_
and i would know that george had phone number 123456789.
the sql for that is:
select …. cast(group_concat(ifnull(client_name,’_')) as char), cast(group_concat(ifnull(phone,’_')) as char), cast(group_concat(ifnull(mobile,’_')) as char) … from clients_table …