×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Converting a single comma separated field into multiple rows

Converting a single comma separated field into multiple rows

Converting a single comma separated field into multiple rows

(OP)
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

CODE --> 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):

CODE --> SQL

+-------------------------------------------------------------------------------------------------------------------------------+
| id    | name         | VALUE          |  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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close