My PostgreSQL table:
create table your_table(name,cc)as values
('a', 2)
,('b', 3)
,('c', 4)
,('d', 3);
I need to get a result like this:
name | cc |
---|---|
a | 2 |
a | 2 |
b | 3 |
b | 3 |
b | 3 |
c | 4 |
c | 4 |
c | 4 |
c | 4 |
d | 3 |
d | 3 |
d | 3 |
It's the rows with name
and cc
, each repeated cc
times.
How can I do it in SQL? Should I use python instead?
You can cross join
with a set-returning function generate_series()
. All it does is spawn rows, and you can ask it to generate cc
amount of them for each row of your table.
demo at db<>fiddle
select name,cc
from your_table
cross join lateral generate_series(1,greatest(cc,1));
LATERAL
is there to underline that the second FROM
object references data from the first one, which normally isn't possible.
The greatest(cc,1)
might be handy in case your cc
can be null
, 0
or negative, if you always want at least a single output row for each input row:
name | cc |
---|---|
a | 2 |
a | 2 |
b | 3 |
b | 3 |
b | 3 |
c | 4 |
c | 4 |
c | 4 |
c | 4 |
d | 3 |
d | 3 |
d | 3 |
e | null |
f | 0 |
g | -1 |
In practice, SRF calls act like lateral
is always implied, so it's optional - and so is the cross join
:
select name,cc
from your_table, generate_series(1,cc);
But the comma makes it a widely disliked old-style join syntax, so the explicit variant is preferred.