Concatenate multiple result rows of one column into one, group by another column [duplicate]

by

Simpler with the aggregate function string_agg() (Postgres 9.0 or later):

SELECT movie, string_agg(actor, ', ') AS actor_list
FROM   tbl
GROUP  BY 1;

The 1 in GROUP BY 1 is a positional reference and a shortcut for GROUP BY movie in this case.

string_agg() expects data type text as input. Other types need to be cast explicitly (actor::text) – unless an implicit cast to text is defined – which is the case for all other string types (varchar, character, name, …) and some other types.

As isapir commented, you can add an ORDER BY clause in the aggregate call to get a sorted list – should you need that. Like:

SELECT movie, string_agg(actor, ', ' ORDER BY actor) AS actor_list
FROM   tbl
GROUP  BY 1;

But it’s typically faster to sort rows in a subquery. See:

More Related Contents:

  1. Aggregate columns with additional (distinct) filters
  2. PostgreSQL: running count of rows for a query ‘by minute’
  3. Two SQL LEFT JOINS produce incorrect result
  4. Create a pivot table with PostgreSQL
  5. Return multiple columns of the same row as JSON array of objects
  6. How to include “zero” / “0” results in COUNT aggregate?
  7. GROUP BY + CASE statement
  8. Best way to count rows by arbitrary time intervals
  9. Selecting data into a Postgres array
  10. Compute percents from SUM() in the same SELECT sql query
  11. Get the distinct sum of a joined table column
  12. Aggregate a single column in query with many columns
  13. Multiple array_agg() calls in a single query
  14. Postgres window function and group by exception
  15. Count cumulative total in Postgresql
  16. Truncate timestamp to arbitrary intervals
  17. How to return result of a SELECT inside a function in PostgreSQL?
  18. Pass multiple values in single parameter
  19. Custom SERIAL / autoincrement per group of values
  20. How do I determine the last day of the previous month using PostgreSQL?
  21. PL/pgSQL checking if a row exists
  22. Why can’t you mix Aggregate values and Non-Aggregate values in a single SELECT?
  23. How much disk-space is needed to store a NULL value using postgresql DB?
  24. SQL comma-separated row with Group By clause
  25. PostgreSQL convert columns to rows? Transpose?
  26. PostgreSQL delete with inner join
  27. Generate DEFAULT values in a CTE UPSERT using PostgreSQL 9.3
  28. Return rows from INSERT with ON CONFLICT without needing to update
  29. PostgreSQL nested INSERTs / WITHs for foreign key insertions
  30. Fetch records that are non zero after the decimal point in PostgreSQL

Leave a Comment