jlsalles jlsalles - 2 months ago 4
MySQL Question

Is there a way to create multiple collumns using the max() function in MYSQL?

I'm trying to create a new table from a sub table using the following formula:

create table if not exists `new_table` as
select * from (
select descrip,
max(case when ID = 1.01 then value else 0 end) 1.01
from(
select ID, `JUL-08` value, 1 descrip
from original_table
union all
select ID, `AGO-08` value, 2 descrip
from original_table
union all
select ID, `SET-08` value, 3 descrip
from original_table
union all
select ID, `OUT-08` value, 4 descrip
from original_table
union all
select ID, `NOV-08` value, 5 descrip
from original_table
union all
select ID, `DEZ-08` value, 6 descrip
from original_table
) src
group by descrip
) as `new_table`;


the formula works well, it creates the table it is intended to create, but i wonder if the max function could be used to create more than 1 collumn for the same table, or if i have to repeat the whole formula for each ID there is to make a new table or either repeat the max() function in the same formula.

Answer

You can repeat expressions as often as you like. You will need to give them different names.

I also note that you have too many subqueries in your expression:

create table if not exists `new_table` as
    select descrip,
           max(case when ID = 1.01 then value else 0 end) as `1.01`,
           max(case when ID = 2.01 then value else 0 end) as `2.01`
    from (select ID, `JUL-08` as value, 1 as descrip 
          from original_table
          union all
          select ID, `AGO-08` as value, 2 as descrip
          from original_table
          union all
          select ID, `SET-08` as value, 3 as descrip
          from original_table
          union all
          select ID, `OUT-08` as value, 4 as descrip 
          from original_table
          union all
          select ID, `NOV-08` as value, 5 as descrip 
          from original_table
          union all
          select ID, `DEZ-08` as value, 6 as descrip 
          from original_table
        ) src
    group by descrip;

I don't advise you to name your columns as numbers (or SQL keywords for that matter). But if you do, you should use escape characters to be clear on what you are doing. I would recommend something more like id_1_01 so the column name does not need to be escaped.

Comments