MySQL incremental columns

Question

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

Look into [EAV](http://stackoverflow.com/questions/tagged/entity-attribute-value) (I wouldn't but maybe you want to). Or normalize your data with [Junction Tables](http://stackoverflow.com/a/32620163) or association tables — Drew, Sep 04 '16 at 19:53
As for your top question, it would require something like a stored proc. It would go sense the schema structure, construct dynamic sql into a PREPARE, and execute it. And it would be slow due to the overhead. — Drew, Sep 04 '16 at 19:56

Gordon Linoff · Accepted Answer · 2016-09-04T22:44:22.870

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.

Could you give me an example of the separate table because i'm envisioning a table with an ever increasing number of columns. — Edward, Sep 04 '16 at 19:30

score 0 · Answer 2 · answered Sep 04 '16 at 19:30

0

There's no way to wildcard select columns.

What you could do:

Setup your table as

ID, Prototype_type, Prototype_name

Then use GROUP_CONCAT:

SELECT id, GROUP_CONCAT(Prototype_name SEPARATOR ',')
FROM table GROUP BY Prototype_name

answered Sep 04 '16 at 19:30

Alexey Soshin

16,718
2
31
40

score 0 · Answer 3 · answered Sep 04 '16 at 22:17

"Should I setup my table in a different format?"

Yes. Your table might look as follows:

ID Prototype_Code Prototype 
------------------------------
 1 A              Fast381A  
 1 B              Blue4812  
 1 C              Green7181 
 1 D              White4812 
 2 A              Slow841C  
 2 B              Orange8312
 3 A              Plane281K

MySQL incremental columns

3 Answers3