PostgreSQL views: Flattening records into a column

Question

I want to create a view that will display interesting stuff about the system users. Among other things, my DB has this table that has several rows per user, ranging basically from 0 to 3 rows. Each such row has a string field called "name", and I'd like my view to contain all of these comma-separated. For example:

UID   Name    Concatenation
1     John    A, C
2     Jack    B, C
3     James   
4     Jill    B

Is there a way to select from the other table into this column? I'm using PostgreSQL but this strikes me as a generic SQL question.

possible duplicate of [How to concatenate strings of a string field in a PostgreSQL 'group by' query?](http://stackoverflow.com/questions/43870/how-to-concatenate-strings-of-a-string-field-in-a-postgresql-group-by-query) — Adam Matan, Dec 08 '10 at 11:07

score 2 · Accepted Answer · edited May 23 '17 at 12:18

See here:

How to concatenate strings of a string field in a PostgreSQL 'group by' query?

You should define a new aggregate function. The exact function depends on your table structure, but here's an example from the PostgreSQL forums:

  CREATE AGGREGATE textcat_all(
      basetype    = text,
      sfunc       = textcat,
      stype       = text,
      initcond    = ''
  );

You can use this new aggregate in your query. For example:

  SELECT partner.name, textcat_all(phones.number || ', ')
      FROM partner LEFT JOIN phones ON partner.id = phones.partner_id
      GROUP BY partner.name;

score 0 · Answer 2 · answered Dec 08 '10 at 11:09

you can use some sort of string concatenation aggregate for example:

create aggregate concat( basetype = text, 
                         sfunc = textcat, 
                         stype = text, 
                         initcond = '' );

select name, substring(concat(', '||value, 3) from t group by name;

but you need 9.0 if you want to use an order by clause in the aggregate

PostgreSQL views: Flattening records into a column

2 Answers2