On Tue, Jun 2, 2009 at 11:52 AM, Ray <r...@stilltech.net> wrote: > Hello, > > I've tried the manual and google, but I am not even sure what to call what I > want to do. > > simplified data example: > I have a table of start and end times for an event, and an id for that event > in a table. each event may occur multiple times, but never more than 5 times > and rarely more than 3. > I want a query that will provide one record per event with all times included. > feel free to answer RTFM or STFW as long as you provide the manual section or > key words. ;) > Thanks, > Ray > > > chart form follows: > > id | event_id | start | end > --------------------------------------- > 1 | 4 | t1 | t2 > 2 | 4 | t3 | t4 > 3 | 4 | t5 | t6 > 4 | 5 | t1 | t2 > 5 | 5 | t3 | t4 > > becomes > > id | event_id | start | end | start | end | start | end > --------------------------------------------------------------------------- > ? | 4 | t1 | t2 | t3 | t4 | t5 | t6 > ? | 5 | t1 | t2 | t3 | t4 >
I think what you are looking for is GROUP_CONCAT. You can just GROUP BY event id, and then process the resulting delimited string on the front end. SELECT event_id, GROUP_CONCAT(start) start_dates, GROUP_CONCAT(end) end_dates FROM events GROUP BY event_id Or even combined start and end dates into a single string and group them. SELECT event_id, GROUP_CONCAT( CONCAT(start, '-', end) ) start_end FROM events GROUP BY event_id But, if you really want to get it in the column format you indicate, you can make a much more complicated query. Use SUBSTRING_INDEX to split out the parts of the group you need. SELECT event_id, SUBSTRING_INDEX(GROUP_CONCAT(start), ',', 1 ) start1, SUBSTRING_INDEX(GROUP_CONCAT(end), ',', 1 ) end1, SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(start), ',', 2 ), ',', -1 ) start2, SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(end), ',', 2 ), ',', -1 ) end2, SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(start), ',', 3 ), ',', -1 ) start3, SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(end), ',', 3 ), ',', -1 ) end3, SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(start), ',', 4 ), ',', -1 ) start4, SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(end), ',', 4 ), ',', -1 ) end4, SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(start), ',', 5 ), ',', -1 ) start5, SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(end), ',', 5 ), ',', -1 ) end5 FROM events GROUP BY event_id; I think that will give the format you specified, but I am not recommending you do it this way. Hope that helps. Brent Baisley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
