Mysql needs to duplicate records using select query

Question

I have a post table like following,

   | ID |   TITLE   |  NUMBER_OF_REPEAT
   |  1 |   post1   |         2
   |  2 |   post2   |         1
   |  3 |   post3   |         3

From the above table I need a select query to produce the row depending upon NUMBER_OF_REPEAT field.

Expected output,

   | ID |   TITLE   
   |  1 |   post1 
   |  1 |   post1   
   |  2 |   post2   
   |  3 |   post3 
   |  3 |   post3 
   |  3 |   post3

This kind of duplication should support the pagination also. Please help me.

Thanks in advance.

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) — hjpotter92, Nov 20 '13 at 10:29

score 0 · Answer 1 · answered Nov 20 '13 at 10:30

0

I don't think this should do by DBMS. this can do in programe. But you can create another table with only two rows and join with post table

let's say table dup with two rows.

SELECT ID ,TITLEfrom post, dup

answered Nov 20 '13 at 10:30

Lakmal Vithanage

2,767
7
42
58

Mihai · Accepted Answer · 2013-11-20T10:43:55.777

0

Something like this

SQL Fiddle

create table numbers (number int);

insert into numbers 
select 1
union 
select 2
union 
select 3;

SELECT id, title
FROM tabl
    JOIN Numbers 
        ON tabl.repeater >= Numbers.number
order by id

Its messy,but modify the numbers table for more repeats.

SQL Fiddle

edited Nov 20 '13 at 10:43

answered Nov 20 '13 at 10:38

Mihai

26,325
7
66
81

Mysql needs to duplicate records using select query

2 Answers2