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

Question

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!

Mike Organek · Accepted Answer · 2020-09-16 09:57:29Z

2

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);

answered Sep 16, 2020 at 9:57

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related