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