[thelist] SQL Group By question

Ken Snyder ksnyder at coremr.com
Wed Dec 6 14:49:25 CST 2006


Casey Crookston wrote:
> If a table looks like this:
>
> ID         Name    Group
> 1          NameA Group1
> 2          NameB Group1
> 3          NameC Group2
> 4          NameD Group2
> Is it possible to build a query that would return these results: 
>
> Group1
> NameA
> NameB
>
> Group2
> NameC
> NameD
>   

In MySQL you can use GROUP_CONCAT(): 
(http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html)

SELECT table.Group, GROUP_CONCAT(table.Name) AS CommaList
FROM table
WHERE 1
GROUP BY table.Group

... returns:

Name   | CommaList
Group1 | NameA,NameB
Group2 | NameC,NameD

Outside of MySQL, I believe that you have two mainstream options:
1. Select both fields then group on the client side
2. Perform one query for each Group

--Ken



More information about the thelist mailing list