Edward Edward - 3 months ago 8
MySQL Question

MySQL incremental columns

I have a table like below:

ID|Prototype_A|Prototype_B|Prototype_C|Prototype_D|
---------------------------------------------------
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
    select(Prototype_*)
    or should I setup my table in a different format?

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

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

    Answer

    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.

    EDIT:

    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.