[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