Duplicate (repeat) rows in sql query result

Question

Let's say I have a table with two rows

 id | value |
----+-------+
 1  |   2   |
 2  |   3   |

I want to write a query that will duplicate (repeat) each row based on the value.
I want this result (5 rows total):

 id | value |
----+-------+
 1  |   2   |
 1  |   2   |
 2  |   3   |
 2  |   3   |
 2  |   3   |

I'm using PostgreSQL 9.4.

Gordon Linoff · Accepted Answer · 2016-02-09 13:26:20Z

12

You can use generate_series():

select t.id, t.value
from (select t.id, t.value, generate_series(1, t.value)
      from t 
     ) t;

You can do the same thing with a lateral join:

select t.id, t.value
from t, lateral
     generate_series(1, t.value);

answered Feb 9, 2016 at 13:24

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1