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?
2
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.
1