CODEWITHSUNDEEP
postgresql
Jordan Parmer
Jordan Parmer

Reputation: 37164

How do I consolidate unique row values into a single column across multiple columns?

I'm performing a series of aggregations on a number of rows. I'm grouping by a common column, but I have minor fields that I want to consolidate all unique values into a single column.

The example below doesn't have the aggregate calculations to simplify the problem.

For instance:

Table A
  table_a_id integer,
  column_1 integer,
  column_2 text,
  column_3 integer

SELECT table_a_id, column1, column_2, column_3
FROM table_a;

Yields

101  |  1  | 'sample value 1'  | 20
101  |  2  | 'sample value 2'  | 25
101  |  3  | 'sample value 3'  | 27

I want:

101  | 1, 2, 3  | 'sample value 1, sample value 2, sample value 3'  | 20, 25, 27

How do I do this in PostgreSQL?

Upvotes: 1

Views: 77

Answers (1)

Jordan Parmer
Jordan Parmer

Reputation: 37164

select
  table_a_id,
  array_to_string(array_agg(distinct column_1), ',') AS the_ones,
  array_to_string(array_agg(distinct column_2), ',') AS the_twos,
  array_to_string(array_agg(distinct column_3), ',') AS the_threes
from table_a 
group by table_a_id
;

Upvotes: 2

Related Questions