<p>I am looking for a way to concatenate the strings of a field within a group by query. So for example, I have a table:</p>

<pre><code>ID   COMPANY_ID   EMPLOYEE
1    1            Anna
2    1            Bill
3    2            Carol
4    2            Dave
</code></pre>

<p>and I wanted to group by company_id to get something like:</p>

<pre><code>COMPANY_ID   EMPLOYEE
1            Anna, Bill
2            Carol, Dave
</code></pre>

<p>There is a built-in function in mySQL to do this <a href="http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat" rel="noreferrer">group_concat</a></p>

[{'answer_id': 43889, 'author': 'Guy C', 'author_id': 4045, 'author_profile': 'https://Stackoverflow.com/users/4045', 'pm_score': 4, 'selected': False, 'text': '<p>I claim no credit for the answer because I found it after some searching:</p>\n\n<p>What I didn\'t know is that PostgreSQL allows you to define your own aggregate functions with <a href="http://www.postgresql.org/docs/8.3/interactive/sql-createaggregate.html" rel="noreferrer">CREATE AGGREGATE</a></p>\n\n<p><a href="http://archives.postgresql.org/pgsql-novice/2003-09/msg00177.php" rel="noreferrer">This post</a> on the PostgreSQL list shows how trivial it is to create a function to do what\'s required:</p>\n\n<pre><code>CREATE AGGREGATE textcat_all(\n  basetype    = text,\n  sfunc       = textcat,\n  stype       = text,\n  initcond    = \'\'\n);\n\nSELECT company_id, textcat_all(employee || \', \')\nFROM mytable\nGROUP BY company_id;\n</code></pre>\n'}, {'answer_id': 43944, 'author': 'Neall', 'author_id': 619, 'author_profile': 'https://Stackoverflow.com/users/619', 'pm_score': 10, 'selected': True, 'text': '<h3>PostgreSQL 9.0 or later:</h3>\n<p>Modern Postgres (since 2010) has the <a href="http://www.postgresql.org/docs/current/static/functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE" rel="noreferrer"><code>string_agg(expression, delimiter)</code></a> function which will do exactly what the asker was looking for:</p>\n<pre><code>SELECT company_id, string_agg(employee, \', \')\nFROM mytable\nGROUP BY company_id;\n</code></pre>\n<p>Postgres 9 also added the ability to specify an <code>ORDER BY</code> clause <a href="https://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-AGGREGATES" rel="noreferrer">in any aggregate expression</a>; otherwise you have to order all your results or deal with an undefined order. So you can now write:</p>\n<pre><code>SELECT company_id, string_agg(employee, \', \' ORDER BY employee)\nFROM mytable\nGROUP BY company_id;\n</code></pre>\n<h3>PostgreSQL 8.4.x:</h3>\n<p>PostgreSQL 8.4 (in 2009) introduced <a href="http://www.postgresql.org/docs/8.4/interactive/functions-aggregate.html" rel="noreferrer" title="array_agg(expression)">the aggregate function <code>array_agg(expression)</code></a> which collects the values in an array. Then <code>array_to_string()</code> can be used to give the desired result:</p>\n<pre><code>SELECT company_id, array_to_string(array_agg(employee), \', \')\nFROM mytable\nGROUP BY company_id;\n</code></pre>\n<h3>PostgreSQL 8.3.x and older:</h3>\n<p>When this question was originally posed, there was no built-in aggregate function to concatenate strings. The simplest custom implementation (<a href="http://archives.postgresql.org/pgsql-novice/2003-09/msg00177.php" rel="noreferrer">suggested by Vajda Gabo in this mailing list post</a>, among many others) is to use the built-in <code>textcat</code> function (which lies behind the <code>||</code> operator):</p>\n<pre><code>CREATE AGGREGATE textcat_all(\n  basetype    = text,\n  sfunc       = textcat,\n  stype       = text,\n  initcond    = \'\'\n);\n</code></pre>\n<p><a href="http://www.postgresql.org/docs/8.3/static/sql-createaggregate.html" rel="noreferrer">Here is the <code>CREATE AGGREGATE</code> documentation.</a></p>\n<p>This simply glues all the strings together, with no separator. In order to get a &quot;, &quot; inserted in between them without having it at the end, you might want to make your own concatenation function and substitute it for the &quot;textcat&quot; above. Here is one I put together and tested on 8.3.12:</p>\n<pre><code>CREATE FUNCTION commacat(acc text, instr text) RETURNS text AS $$\n  BEGIN\n    IF acc IS NULL OR acc = \'\' THEN\n      RETURN instr;\n    ELSE\n      RETURN acc || \', \' || instr;\n    END IF;\n  END;\n$$ LANGUAGE plpgsql;\n</code></pre>\n<p>This version will output a comma even if the value in the row is null or empty, so you get output like this:</p>\n<pre><code>a, b, c, , e, , g\n</code></pre>\n<p>If you would prefer to remove extra commas to output this:</p>\n<pre><code>a, b, c, e, g\n</code></pre>\n<p>Then add an <code>ELSIF</code> check to the function like this:</p>\n<pre><code>CREATE FUNCTION commacat_ignore_nulls(acc text, instr text) RETURNS text AS $$\n  BEGIN\n    IF acc IS NULL OR acc = \'\' THEN\n      RETURN instr;\n    ELSIF instr IS NULL OR instr = \'\' THEN\n      RETURN acc;\n    ELSE\n      RETURN acc || \', \' || instr;\n    END IF;\n  END;\n$$ LANGUAGE plpgsql;\n</code></pre>\n'}, {'answer_id': 354009, 'author': 'bortzmeyer', 'author_id': 15625, 'author_profile': 'https://Stackoverflow.com/users/15625', 'pm_score': 3, 'selected': False, 'text': '<p>As already mentioned, creating your own aggregate function is the right thing to do. Here is my concatenation aggregate function (you can find <a href="http://www.bortzmeyer.org/agregats-postgresql.html" rel="noreferrer">details in French</a>): </p>\n\n<pre><code>CREATE OR REPLACE FUNCTION concat2(text, text) RETURNS text AS \'\n    SELECT CASE WHEN $1 IS NULL OR $1 = \\\'\\\' THEN $2\n            WHEN $2 IS NULL OR $2 = \\\'\\\' THEN $1\n            ELSE $1 || \\\' / \\\' || $2\n            END; \n\'\n LANGUAGE SQL;\n\nCREATE AGGREGATE concatenate (\n  sfunc = concat2,\n  basetype = text,\n  stype = text,\n  initcond = \'\'\n</code></pre>\n\n<p>);</p>\n\n<p>And then use it as:</p>\n\n<pre><code>SELECT company_id, concatenate(employee) AS employees FROM ...\n</code></pre>\n'}, {'answer_id': 528108, 'author': 'Kev', 'author_id': 16777, 'author_profile': 'https://Stackoverflow.com/users/16777', 'pm_score': 3, 'selected': False, 'text': '<p>This latest announcement list snippet might be of interest if you\'ll be upgrading to 8.4:</p>\n\n<blockquote>\n  <p>Until 8.4 comes out with a\n  super-effient native one, you can add\n  the array_accum() function in the\n  PostgreSQL documentation for rolling\n  up any column into an array, which can\n  then be used by application code, or\n  combined with array_to_string() to\n  format it as a list:</p>\n  \n  <p><a href="http://www.postgresql.org/docs/current/static/xaggr.html" rel="noreferrer">http://www.postgresql.org/docs/current/static/xaggr.html</a></p>\n</blockquote>\n\n<p>I\'d link to the 8.4 development docs but they don\'t seem to list this feature yet.</p>\n'}, {'answer_id': 563907, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 2, 'selected': False, 'text': '<p>I found this PostgreSQL documentation helpful: <a href="http://www.postgresql.org/docs/8.0/interactive/functions-conditional.html" rel="nofollow noreferrer">http://www.postgresql.org/docs/8.0/interactive/functions-conditional.html</a>.</p>\n\n<p>In my case, I sought plain SQL to concatenate a field with brackets around it, if the field is not empty.</p>\n\n<pre><code>select itemid, \n  CASE \n    itemdescription WHEN \'\' THEN itemname \n    ELSE itemname || \' (\' || itemdescription || \')\' \n  END \nfrom items;\n</code></pre>\n'}, {'answer_id': 882375, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 3, 'selected': False, 'text': "<p>Following up on Kev's answer, using the Postgres docs:</p>\n\n<p>First, create an array of the elements, then use the built-in <code>array_to_string</code> function.</p>\n\n<pre><code>CREATE AGGREGATE array_accum (anyelement)\n(\n sfunc = array_append,\n stype = anyarray,\n initcond = '{}'\n);\n\nselect array_to_string(array_accum(name),'|') from table group by id;\n</code></pre>\n"}, {'answer_id': 1374479, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 3, 'selected': False, 'text': '<p>Following yet again on the use of a custom aggregate function of string concatenation: you need to remember that the select statement will place rows in any order, so you will need to do a sub <em>select</em> in the <em>from</em> statement with an <em>order by</em> clause, and then an outer <em>select</em> with a <em>group by</em> clause to aggregate the strings, thus:</p>\n\n<pre><code>SELECT custom_aggregate(MY.special_strings)\nFROM (SELECT special_strings, grouping_column \n        FROM a_table \n        ORDER BY ordering_column) MY\nGROUP BY MY.grouping_column\n</code></pre>\n'}, {'answer_id': 2292862, 'author': 'Markus Döring', 'author_id': 276551, 'author_profile': 'https://Stackoverflow.com/users/276551', 'pm_score': 7, 'selected': False, 'text': "<p>How about using Postgres built-in array functions? At least on 8.4 this works out of the box:</p>\n\n<pre><code>SELECT company_id, array_to_string(array_agg(employee), ',')\nFROM mytable\nGROUP BY company_id;\n</code></pre>\n"}, {'answer_id': 6139500, 'author': 'dirbacke', 'author_id': 435140, 'author_profile': 'https://Stackoverflow.com/users/435140', 'pm_score': 5, 'selected': False, 'text': '<p>As from PostgreSQL 9.0 you can use the aggregate function called <a href="http://developer.postgresql.org/pgdocs/postgres/functions-aggregate.html" rel="noreferrer">string_agg</a>. Your new SQL should look something like this: <pre><code>SELECT company_id, string_agg(employee, \', \')\nFROM mytable\nGROUP BY company_id;</code></pre></p>\n'}, {'answer_id': 45433428, 'author': 'Gobinath', 'author_id': 7368539, 'author_profile': 'https://Stackoverflow.com/users/7368539', 'pm_score': 0, 'selected': False, 'text': "<p>According to version PostgreSQL 9.0 and above you can use the aggregate function called string_agg. Your new SQL should look something like this:</p>\n\n<pre><code>SELECT company_id, string_agg(employee, ', ')\n    FROM mytable GROUP BY company_id;\n</code></pre>\n"}, {'answer_id': 52096061, 'author': 'Sandip Debnath', 'author_id': 1459714, 'author_profile': 'https://Stackoverflow.com/users/1459714', 'pm_score': 0, 'selected': False, 'text': "<p>You can also use format function. Which can also implicitly take care of  type conversion of text, int, etc by itself.</p>\n\n<pre><code>create or replace function concat_return_row_count(tbl_name text, column_name text, value int)\nreturns integer as $row_count$\ndeclare\ntotal integer;\nbegin\n    EXECUTE format('select count(*) from %s WHERE %s = %s', tbl_name, column_name, value) INTO total;\n    return total;\nend;\n$row_count$ language plpgsql;\n\n\npostgres=# select concat_return_row_count('tbl_name','column_name',2); --2 is the value\n</code></pre>\n"}, {'answer_id': 53605287, 'author': 'Damien Sawyer', 'author_id': 494635, 'author_profile': 'https://Stackoverflow.com/users/494635', 'pm_score': 0, 'selected': False, 'text': '<p>I\'m using Jetbrains Rider and it was a hassle copying the results from above examples to re-execute because it seemed to wrap it all in JSON. This joins them into a single statement that was easier to run </p>\n\n<pre><code>select string_agg(\'drop table if exists "\' || tablename || \'" cascade\', \';\') \nfrom pg_tables where schemaname != $$pg_catalog$$ and tableName like $$rm_%$$\n</code></pre>\n'}, {'answer_id': 55178427, 'author': 'Gapp', 'author_id': 11207468, 'author_profile': 'https://Stackoverflow.com/users/11207468', 'pm_score': 1, 'selected': False, 'text': "<p>If you are on Amazon Redshift, where string_agg is not supported, try using listagg.</p>\n\n<pre><code>SELECT company_id, listagg(EMPLOYEE, ', ') as employees\nFROM EMPLOYEE_table\nGROUP BY company_id;\n</code></pre>\n"}, {'answer_id': 55659734, 'author': 'Valentin Podkamennyi', 'author_id': 5438323, 'author_profile': 'https://Stackoverflow.com/users/5438323', 'pm_score': 3, 'selected': False, 'text': '<p>Use <code>STRING_AGG</code> function for <a href="https://www.postgresql.org/docs/current/functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE" rel="noreferrer">PostgreSQL</a> and <a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#string_agg" rel="noreferrer">Google BigQuery SQL</a>:</p>\n\n<pre><code>SELECT company_id, STRING_AGG(employee, \', \')\nFROM employees\nGROUP BY company_id;\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/43870', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/4045/']