user3693606 user3693606 - 10 months ago 50
MySQL Question

multiple case statements with same logic in when

I want to use case statement for each column in mysql. The logic inside when of each case statement is same which is a long list. Is there a way to write this optimally. like

case when cond1
then 'xyz' as col1,
'xyz2' as col2,
'uuy' as col3
else null
for all each column end.


Short Answer: No

Long Answer: Kind of. You can play with either wrapping the logic into function and then call it for each column (if applicable) or play with dynamic query - dirty but will work. Consider this:

create table t (
 alef varchar(100),
 bet varchar(100));

set @case := 'case when ''?'' = ''A'' then 1 else 0 end';

set @sql := concat('select ',replace(@case,'?','alef'),',',replace(@case,'?','bet'),' from t');

prepare stmt from @sql;

execute stmt;

Finally if you're really lazy ;) you can iterate through information_schema.columns view and dynamically create the select statement with s.t. along these lines:

set @subCase := '';
select @subCase := concat(@subCase,replace(@case,'?',column_name),',') from information_schema.columns where table_name = 't';

And these code can be probably safely wrapped in procedure.