Before I get into some kinda cool examples, I just want to be sure reader's understand what this aggregate function does.
\n\t <\/p>\n
A Brief GROUP_CONCAT Intro<\/h2>\n
You probably know about other aggregate functions:<\/p>\n
- \n
- SUM gets the sum of all values in a group<\/li>\n
- COUNT returns the number of items in the group<\/li>\n
- AVG grabs the average of all values in the group<\/li>\n<\/ul>\n
We use these functions in conjunction with the GROUP BY statement. I'll stop there, you're not looking for a basic lesson in SQL here. So let me introduce the GROUP_CONCAT function.<\/p>\n
GROUP_CONCAT is an aggregate function like the others. And, like the other aggregate functions, it returns only one value. The big difference though is that the value it returns is actually a string, a string that is a concatenation of all values in the group.<\/p>\n
Let's say you have a table of users<\/strong><\/em>, and each user can have one or more orders, stored in an orders <\/strong><\/em>table.<\/p>\n
You are able to aggregate a list of all of a user's order numbers into one field, using GROUP_CONCAT.<\/p>\n
SELECT users.username, GROUP_CONCAT(orders.order_id) as ordernums\r\nFROM users\r\nINNER JOIN orders\r\n ON orders.user_id = users.user_id\r\nGROUP BY users.user_id<\/code><\/pre>\nThis will return a result set similar to:<\/p>\n
Username Ordernums<\/strong>
\n\ttestuser1 5,16,32,103
\n\ttestuser2 78,101
\n\ttestuser3 104,106<\/p>\n<\/p>\n
Real World GROUP_CONCAT Examples<\/h2>\n
Now that you have an idea what GROUP_CONCAT does, for the purposes of solidifying that knowledge, let's have a look at some interesting ways to use it. These are real-life examples, ways I've actually employed GROUP_CONCAT in my development.
\n\t <\/p>\nDirect to Semantic HTML<\/h3>\n
The HTML definition list<\/em> (now called a description list <\/em>in HTML 5) isn't used quite as often as it should be. I imagine it's a mix of it being more difficult to style than other tags and it's lack of understanding. It should be used more often to define relationships between a term and its definition or description. The MySQL GROUP_CONCAT function makes really easy work of directly outputting valid HTML in the form of a definition list.<\/p>\n
Let's say we have a table like this:<\/p>\n
Name Job Title
\n\t<\/strong> Dan Aykroyd Vice-President
\n\tChevy Chase Account Executive
\n\tBilly Crystal Systems Analyst<\/p>\nWe're looking for a nice easy way to display a list of job titles, along with the person that holds that job title. We could use the traditional-style query that returns three result rows, or we could go with a query that aggregates all results into a single row, and formats it as HTML!<\/p>\n
SELECT \r\nGROUP_CONCAT('<dt>',name, '<\/dt><dd>', job_title, '<\/dd>' SEPARATOR '\\n') as jobs\r\nFROM people\r\n<\/code><\/pre>\nThis will return a single row, with a single field that contains a nice little piece of HTML. HTML that we can directly output without any processing:<\/p>\n
<dt>Dan Aykroyd<\/dt><dd>Vice-President<\/dd>\r\n<dt>Chevy Chase<\/dt><dd>Account Executive<\/dd>\r\n<dt>Billy Crystal<\/dt><dd>Systems Analyst<\/dd><\/code><\/pre>\nOf course, this type of use should be considered when a script depending on those query results is expecting HTML output.<\/p>\n
<\/p>\n
Avoiding Multiple Queries and Nested Loops<\/h3>\n
![\"group_concat](\"http:\/\/www.sitekickr.com\/blog\/wp-content\/uploads\/2013\/10\/group-concat-no-nested-loops.png\")
<\/a><\/p>\n