Create multiple records in MySQL table based on value of one field.

I am looking to create multiple records based on a single value in a field in MySQL.

I have some data that looks like this in a table in MySQL

item_id | grade
12345 | 01
12346 | 05
12347 | 09-12

I need to transform it into something that looks like this:
12345 | 01
12346 | 05
12347 | 09
12347 | 10
12347 | 11
12347 | 12

Where 09-12 is can be a range of values
09-10
11-12
06-08
etc.
LVL 2
musickmannData AnalystAsked:
Who is Participating?
 
theGhost_k8Database ConsultantCommented:
Long back I wrote a stored procedure to split delimited string values of a field to rows. I'm sure with little tweak you can make it work for your requirement..
0
 
Ares KurkluSoftware EngineerCommented:
First of all you will need a numbers table you can auto fill that until 100 or so depending on the max range like 09-10

i don't know your table name but i called it item the following should work, you can just replace item with your table name and make sure there is no table named numbers

drop table if exists numbers;
create table numbers
(number int not null auto_increment primary key);
insert into numbers values (),(),(),(),(),(),(),(),(),();
insert into numbers values (),(),(),(),(),(),(),(),(),();
insert into numbers values (),(),(),(),(),(),(),(),(),();
insert into numbers values (),(),(),(),(),(),(),(),(),();
insert into numbers values (),(),(),(),(),(),(),(),(),();
insert into numbers select number + 50 from numbers;
select * from numbers;
 

then you can run the following to get the output you want again you need to fix the table name
 
      SELECT item_id,
       grade, grade  as number
     from item
     where grade not like '%-%'
     UNION

 SELECT item_id,
       grade,LPAD(NUMBER, 2, '0')
     FROM (
       SELECT item_id,
       grade,
#       SUBSTRING( grade,1,POSITION('-' IN grade) -1),
#      CONVERT(SUBSTRING( grade,POSITION('-' IN grade)+1, LENGTH (grade) -POSITION('-' IN grade) ), UNSIGNED)
#      - CONVERT(SUBSTRING( grade,1,POSITION('-' IN grade) -1) , UNSIGNED),
      CONVERT (SUBSTRING( grade,1,POSITION('-' IN grade) -1), UNSIGNED) + number -1
      number
      FROM item
            JOIN Numbers
                  ON CONVERT(SUBSTRING( grade,POSITION('-' IN grade)+1, LENGTH (grade) -POSITION('-' IN grade) ), UNSIGNED)
      - CONVERT(SUBSTRING( grade,1,POSITION('-' IN grade) -1) , UNSIGNED)+1
                  
                  >= Numbers.number
) AS TEST WHERE grade like '%-%' ORDER BY item_id,number;
0
 
musickmannData AnalystAuthor Commented:
Thanks all, I'm still working on implanting these suggestions.
1
 
theGhost_k8Database ConsultantCommented:
Author is requested to choose the best solution. Otherwise the points will be equally split between the two of the commenters offering solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.