I have a table like the following:
| col_A | col_B |
|-------|-------|
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 3 | 1 |
| 3 | 2 |
I want to group and concatenate the results into an array like the following:
| col_A | col_B |
|-------|-------|
| 1,2 | 1,2,3 |
| 3 | 1,2 |
My attempt at writing a query:
SELECT col_A, array_agg(col_B ORDER BY col_B DESC) FROM table GROUP BY col_A;
However, this outputs:
| col_A | col_B |
|-------|---------|
| 1 | {1,2,3} |
| 2 | {1,2,3} |
| 3 | {1,2} |
Seems like you want to aggregate col_b grouped by col_a, and then aggregate col_a grouped by the aggregated arrays. You can do this with a nested query, of which you did the first step already:
SELECT array_agg(col_a), col_b
FROM (SELECT col_a, array_agg(DISTINCT col_b ORDER BY col_b) AS col_b
FROM example GROUP BY col_a) grouped_b
GROUP BY col_b;