How to aggregate specific rows in a column into arrays?

Question

I have table such as

| work_order_id | part_number | due_date   |
|: 1            |:P123:       | 2022-03-04:|
|: 2            |:P123:       | 2022-03-11:|
|: 3            |:P123:       | 2022-04-02:|

Essentially I wanted to create a view where the first and third rows are aggregated by the order of the second column. So the resulting view should look like:

|: {1,2,3}|:P123:| {2022-04-2, 2022-03-11, 2022-04-2:|

Possible Duplicate of: https://stackoverflow.com/questions/7317475/postgresql-array-agg-order — xQbert, Mar 02 '22 at 16:43

score 0 · Answer 1 · answered Mar 02 '22 at 16:39

The Aggregrate function ARRAY_AGG() seems to be what you're after.

SELECT ARRAY_AGG(work_order_ID ORDER BY work_order_ID) as WorkorderIDs
     , part_number
     , ARRAY_AGG(due_Date ORDER BY work_order_ID) as due_dates
FROM yourTableName
GROUP BY part_number

How to aggregate specific rows in a column into arrays?

1 Answers1