Programming stuff: Bash, Linux, SQL and Java

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 …

Comments(0)