how can i list items in one field MySQL?

Question

i have a table filled with models,the models start year and end year and productid's that are assigned to it

ModelNo | StartYear | EndYear | ProductID |  
0       | 1997      | 2000    | 511777    |  
1       | 1998      | 1998    | 511789    |  

what i'd like is an sql result as follows:

ProductID | Profile  
511777    | 0-1997,0-1998,0-1999,0-2000  
511789    | 1-1998

If anyone has any ideas as to how i could achieve this.

DPERROTT

Answer 1

You'd need to join onto an auxiliary numbers table (containing sequential integers) to bring the missing numbers in the range in. i.e. something like

... FROM Models m
JOIN Numbers n ON n.number >= m.StartYear AND n.number <= m.EndYear

Then use CONCAT to get 0-1997

and GROUP_CONCAT to get 0-1997,0-1998,0-1999,0-2000