Edward Edward - 1 year ago 60
MySQL Question

MySQL incremental columns

I have a table like below:

1 |Fast381A |Blue4812 | Green7181 | White4812 |
2 |Slow841C |Orange8312 | null | null |
3 |Plane281K | null | null | null |

I need my query to return all non null prototypes for that ID.
so for example:

  • 1 : Fast381A,Blue4812,Green7181,White4812

  • 2 : Slow841C,Orange8312

  • 3 : Plane281K

  • Is there a way to wildcard select all columns like
    or should I setup my table in a different format?

    For example I've been taught this type of structure is bad practice:

    1 |Fast381A,Blue4812,Green7181,White4812
    2 |Slow841C,Orange8312
    3 |Plane281K

    Answer Source

    A SQL query returns a fixed set of columns. If you want to combine the non-NULL values into a single column, I would recommend concat_ws():

    select id,
           concat_ws(',', Prototype_A, Prototype_B, Prototype_C, Prototype_D)
    from t;

    This ignores the NULL values. The query returns two columns, one is a list of prototypes.

    And, the answer to your question is "Yes". You should consider changing your data structure. Having multiple columns storing the same thing, with just an index identifying them usually means that you want a separate table, with one row per id and per prototype.


    You want a table like this:

    create table ModelPrototypes (
        ModelProtypeId int primary key auto_increment,
        ModelId int not null,
        ProtoTypeChar char(1),
        Prototype varchar(255)

    Then you would populate it with values like:

    1   A    Fast381A
    1   B    Blue4812
    1   C    Green7181
    1   D    White4812 

    I'm not sure if PrototypeChar is really needed, but the information is in your table.

    Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download