StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The original question was database specific, but perhaps this is a good place to include a more generic answer. It's a common question. The concept that you are describing is often referred to as 'Group Concatenation'. There's no standard solution in SQL-92 or SQL-99. So you'll need a vendor-specific solution.</p> <ul> <li><strong>MySQL</strong> - Probably the simplest solution. Use the built-in GROUP_CONCAT function. In your example you would want something like this:</li> </ul> <pre>select o.ID, o.Address, o.OtherDetails, GROUP_CONCAT( concat(e.firstname, ' ', e.lastname) ) as Employees from employees e inner join organization o on o.org_id=e.org_id group by o.org_id </pre> <ul> <li><strong>PostgreSQL</strong> - <strong>EDIT:</strong> PostgreSQL 9.0 is equally simple now that string_agg(expression, delimiter) is built-in. Here it is with 'comma-space' between elements:</li> </ul> <pre>select o.ID, o.Address, o.OtherDetails, STRING_AGG( (e.firstname || ' ' || e.lastname), ', ' ) as Employees from employees e inner join organization o on o.org_id=e.org_id group by o.org_id </pre> <p>PostgreSQL before 9.0 allows you to define your own aggregate functions with CREATE AGGREGATE. Slightly more work than MySQL, but much more flexible. See this <a href="https://stackoverflow.com/questions/43870/how-to-concatenate-strings-of-a-string-field-in-a-postgresql-group-by-query">other post</a> for more details. (Of course PostgreSQL 9.0 and later have this option as well.)</p> <ul> <li><strong>Oracle & MS SQL Server</strong> - Create a stored procedure that takes the org_id as its input and outputs the concatenated employee names. Then use this stored procedure in your query. Some of the other responses here include some details about how to write stored procedures like these.</li> </ul> <pre>select o.ID, o.Address, o.OtherDetails, MY_CUSTOM_GROUP_CONCAT_PROCEDURE( o.ID ) as Employees from organization o </pre> <ul> <li><strong>Other DBMS technologies</strong> - The stored procedure route is the most likely. Perhaps others can update this answer with more technology specific answers.</li> </ul>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload