mySQL Group By question

Question

Hi how do i group this data:

**Date** **Day** **DURATION** **IDSTAFF**

21-09-2010 Sun      AM           1   
22-09-2010 Mon      AM           1  
21-09-2010 Sun      PM           2  
22-09-2010 Mon      PM           2  

So it will become like this?

**Date** **Day** **DURATION** **IDSTAFF**

21-09-2010 Sun      AM           1 (am),2 (pm)   
22-09-2010 Mon      AM           1 (am),2  (pm)

Using sql group by will only show one id staff. Can sql queries solve this or need to use php?

Answer 1

Use GROUP_CONCAT():

SELECT Date, Day, Duration group_concat(IdStaff + '(' + Duration + ')' SEPARATOR ',')
from yourtable
group by Date, Day, Duration

Answer 2

The correct answer to this problem:

SELECT DATE, DAY, GROUP_CONCAT(IDSTAFF, ' (' , PERIOD , ')' SEPARATOR ', ') AS PERSONNAME FROM LEAVERECORD GROUP BY DATE

If you want to get the name for each idstaff, use the subquery in the group_concat and concat the firstname and lastname. Example:

SELECT DATE, DAY, PERIOD, GROUP_CONCAT((SELECT CONCAT(FIRSTNAME,' ',LASTNAME) from STAFF where STAFF.IDSTAFFTABLE=IDSTAFF), ' (' , PERIOD , ')' SEPARATOR ', ') AS PERSONNAME FROM LEAVERECORD GROUP BY DATE

The result :