group by with new colum

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

TF_TheMoon

Which database are you using?

Its tricky, as really yuo need to to join to the table multiple times in order to get that.

The closet you will get in MySQL is using MySQL 5 GROUP_CONCAT (http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html)

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

mysql> SELECT student_name,
    ->     GROUP_CONCAT(test_score)
    ->     FROM student
    ->     GROUP BY student_name;

Or:

mysql> SELECT student_name,
    ->     GROUP_CONCAT(DISTINCT test_score
    ->               ORDER BY test_score DESC SEPARATOR ' ')
    ->     FROM student
    ->     GROUP BY student_name;