3

My Postgres version is 11.15. I have a table like:

id employee
1 Anna
2 Bill
3 Carol
4 Dave
5 Tom

The output I need is

id cumulative_employees
1 Anna
2 Bill, Anna
3 Carol, Bill, Anna
4 Dave, Carol, Bill, Anna
5 Tom, Dave, Carol, Bill, Anna

I tried using LAG function of PostgreSQL but it only gives me last row value, could not find a way to get all previous rows, number of rows is dynamic and can be anywhere between 12 to 52 rows.

Can you please help me out?

1 Answer 1

4

To get the inverted order of elements in the aggregated string, something like this would do:

SELECT id
     , string_agg(employee, ', ') OVER (ORDER BY id DESC
                                        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM   tbl
ORDER  BY id;

fiddle

Aggregate functions allow a local per-group ORDER BY:

SELECT string_agg(employee, ', ' ORDER BY id DESC) AS cumulative_employees
FROM   tbl;

But the same is not implemented for window functions. Hence the "trick" with shifted window frames to achieve the same.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.