Building a string based on columns from a GROUP

Question

I have a table like this:

user 1    A
user 1    B
user 2    H
user 2    G
user 2    A

and I need a result like:

user 1   AB
user 2  HGA

Is there a way to obtain a result like this?

Answer 1

So here we create some test data

CREATE TABLE foo AS
SELECT * FROM (
    VALUES (1,'A'),(1,'B'),(2,'H'),(2,'G'),(2,'A')
) AS f(id,col);

This should work,

SELECT id, array_to_string(array_agg(col), '')
FROM table
GROUP BY id;

Here is what we're doing,

1. GROUP BY id.
2. Build a PostgreSQL text[] (text array) of that column with array_agg
3. Convert the array back to text by joining on an empty string '' with array_to_string.

You can also use string_agg,

SELECT id, string_agg(col, '')
FROM foo
GROUP BY id;

Answer 2

The better soluction is using str_sum agregate function

select 
user,
str_sum(column_name,'')
from table_name
group by user;