PostgreSQL views: Flattening records into a column

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开发者_StackOverflow中文版 other table into this column? I'm using PostgreSQL but this strikes me as a generic SQL question.

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;

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