MYSQL view containing a row for each element in string

Asked Mar 30 '17 at 14:45

Active Mar 30 '17 at 14:54

Viewed 32 times

I don't know if it's possible but I try to ask you. I've a user table

|ID|production_lines|activity|
|1 |    A|B|C       | X|Y|Z  |
|2 |    B|C|D       | X|Y|Z  |

I need a view displaying this

|production_line|activity|count|
|    A          | X      | 1   |
|    B          | X      | 2   |
|    C          | X      | 2   |
|    D          | X      | 1   |
|    A          | Y      | 1   |
|    B          | Y      | 2   |
|    C          | Y      | 2   |
|    D          | Y      | 1   |

and so on... The "count" columns is the count of how many time i've a combination of this particular production_line and activity.

Thank you very much

edited Mar 30 '17 at 14:54

asked Mar 30 '17 at 14:45

Argo

2

Clearly a bad table design. Look into this for ideas: http://stackoverflow.com/questions/17308669/reverse-group-concat-in-mysql – PM 77-1 Mar 30 '17 at 14:48
2

You should normalise your data structure – Shadow Mar 30 '17 at 14:56
1

your desired view looks like how the table should be probably be structured in the first place. – ADyson Mar 30 '17 at 15:04
I know... I can't do a refactor of this table. I'll do somehting in PHP. – Argo Mar 30 '17 at 15:07

MYSQL view containing a row for each element in string

0 Answers0