CODEWITHSUNDEEP
postgresqlamazon-redshift
hahahahahelp
hahahahahelp

Reputation: 21

Making a dynamic custom series in postgresql (avoiding loop if possible)

im new to postgresql and i'm trying to do something that requires a loop in T-SQL did some research on how to loop in postgresql and found out that i should make some sort of function first and i'm trying to avoid that.

I have a main table of

SELECT 17 as employeecount, 'Aug-2020' as month
UNION
SELECT 22, 'Sep-2020'
UNION
SELECT 27, 'Oct-2020'

I would need an output that increments 1 to x(employeecount) per month like below:

SELECT 1 as employeecount, 'Aug-2020' as month
UNION
SELECT 2, 'Aug-2020'
UNION
SELECT 3, 'Aug-2020'
........... up to 17, 'Aug-2020'
UNION
SELECT 1, 'Sep-2020'
UNION 
... up to 22, 'Sep-2020' and so on

or

---------------------
increment | Month   |
1         | Aug-2020|
2         | Aug-2020|
3         | Aug-2020|
4         | Aug-2020|
.         | Aug-2020|
.         | Aug-2020|
17        | Aug-2020|
1         | Sep-2020|
2         | Sep-2020|
.         | Sep-2020|
.         | Sep-2020|
22        | Sep-2020|

I'm trying to avoid looping but if there's no other way, then it'd be fine.

Thanks in advance!

Upvotes: 0

Views: 45

Answers (1)

Mike Organek
Mike Organek

Reputation: 12484

Use a lateral join to generate_series():

with main (employeecount, month) as (
  values (17, 'Aug-2020'), (22, 'Sep-2020'), (27, 'Oct-2020')
)
select increment, month 
  from main
 cross join lateral generate_series(1, employeecount) as gs(increment);

Upvotes: 2

Related Questions