group by without loosing data

[renebati]

Hello,

(sorry, but my english is not very well. i'm from holland.)

i have a table 'children' like this :

id | child_id | startdate | enddate
------------------------------------------
1 | 1 | jan | jun
2 | 1 | jul | dec
3 | 2 | jan | mar
4 | 2 | apr | nov

Is it possible to group rows with the same child_id, but without loosing de startdate and enddate from other rows?


I want this as output :

id | child_id | startdate1 | enddate1 | startdate2 | enddate2
-------------------------------------------------------------------
1 | 1 | jan | jun | jul | dec
2 | 2 | jan | mar | apr | nov



Is this possible? Thanx to you all.
Greetz Rene

[wickning1]

Best I can come up with is use GROUP_CONCAT to output something like this:

id | child_id | GROUP_CONCAT(startdate SEPARATOR '!') | GROUP_CONCAT(enddate SEPARATOR '!')
-------------------------------------------------------------------
1 | 1 | jan!jul | jun!dec
2 | 2 | jan!apr | mar!nov

See here for more info.. You may need to use an ORDER BY in there too.
[a href=\"http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html\" target=\"_blank\"]http://dev.mysql.com/doc/refman/5.0/en/gro...-functions.html[/a]