How to duplicate rows in mysql

Question

I have a table which is having following data

Region Market    Cluster   Dt-Accept Count
South  Karnataka Bangalore 23-05-2015 5
North  Delhi     NewDelhi  25-08-2015 3

I want a sql statement which should repeat numbers of rows based upon the number stored in count.

Region Market    cluster   Dt-Accept  count
South  Karnataka Bangalore 23-05-2015 5
South  Karnataka Bangalore 23-05-2015 5
South  Karnataka Bangalore 23-05-2015 5
South  Karnataka Bangalore 23-05-2015 5
South  Karnataka Bangalore 23-05-2015 5
North  Delhi     NewDelhi  25-08-2015 3
North  Delhi     NewDelhi  25-08-2015 3
North  Delhi     NewDelhi  25-08-2015 3

How can I achieve this?

mysql cannot create rows out of nothingness. do this in client-side code. — Marc B, Jul 08 '16 at 14:21
Possible duplicate of [SQL: Repeat a result row multiple times, and number the rows](http://stackoverflow.com/questions/10423767/sql-repeat-a-result-row-multiple-times-and-number-the-rows) — duncan, Jul 08 '16 at 14:22
Create a temp table with integers 1 to Max(COUNT) and then join your source table to the temp table where count >= tally integer — Matt, Jul 08 '16 at 15:34

Vignesh Kumar A · Answer 1 · 2016-07-08T15:49:15.930

Try this

    SELECT Region,Market,cluster,Dt-Accept,count
    FROM Table1 T JOIN (
                        SELECT 0 No UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL 
                        SELECT 3   UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL
                        SELECT 6   UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL
                        SELECT 9   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
                        SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL 
                        SELECT 15
                       ) S ON T.count > S.No

Fiddle Demo

How to duplicate rows in mysql

1 Answers1