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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.