Hi,
I have a table that holds different rating categories in a single field - comma separated (table name: scale). Each rating has a value relative to where it is in the list (first_item = 1, second_item = 2, third_item = 3 etc).
I want to be able to run an SQL query in MySQL to split the field into multiple rows and determine the rating number (VALUE).
Table: scale
I want to be able to split this so that each scale is split into a separate row and numbered (VALUE):
I have tried and failed to find a solution ... any help greatly appreciated!
Many thanks,
Gary
I have a table that holds different rating categories in a single field - comma separated (table name: scale). Each rating has a value relative to where it is in the list (first_item = 1, second_item = 2, third_item = 3 etc).
I want to be able to run an SQL query in MySQL to split the field into multiple rows and determine the rating number (VALUE).
Table: scale
SQL:
+-------------------------------------------------------------------------------------------------------------------------------+
| id | name | scale
|-------|--------------|---------------------------------------------------------------------------------------------------------
| 1 | knowing | Mostly separate knowing,Separate and connected,Mostly connected knowing
| 2 | competence | Not yet competent,Competent
| 3 | skill | Artistic, Communication, Leadership, Community engagement, Sport/outdoors, Cultural enrichment, Other
+-------------------------------------------------------------------------------------------------------------------------------+I want to be able to split this so that each scale is split into a separate row and numbered (VALUE):
SQL:
+-------------------------------------------------------------------------------------------------------------------------------+
| id | name | [b]VALUE[/b] | scale
|-------|--------------|---------------------------------------------------------------------------------------------------------
| 1 | knowing | 1 | Mostly separate knowing
| 1 | knowing | 2 | Separate and connected
| 1 | knowing | 3 | Mostly connected knowing
| 2 | competence | 1 | Not yet competent
| 2 | competence | 2 | Competent
| 3 | skill | 1 | Artistic
| 3 | skill | 2 | Communication
| 3 | skill | 3 | Leadership
| 3 | skill | 4 | Community engagement
| 3 | skill | 5 | Sport/outdoors
| 3 | skill | 6 | Cultural enrichment
| 3 | skill | 7 | Other
+-------------------------------------------------------------------------------------------------------------------------------+I have tried and failed to find a solution ... any help greatly appreciated!
Many thanks,
Gary