0

Hello I'm trying to make a query to significantly shorten employee badge stamping tables.

They have one record for each stamp, for example:

Badge   Date        Time    Type
430016  12/12/2016  09:34   In
430016  12/12/2016  13:02   Out
430016  12/12/2016  13:26   In
430016  12/12/2016  17:58   Out
430016  12/12/2016  18:01   In
430016  12/12/2016  19:02   Out

I need a mysql query to put it all in one record i.e.

Badge    Date        In1    Out1    In2     Out2    In3    Out3
0430016  2/12/2016   09:34  13:02   13:26   17:58   18:01  19:02
demongolem
  • 9,474
  • 36
  • 90
  • 105
Fabio Facchetti
  • 1
  • `GROUP BY` will help you here. – Xatenev Dec 27 '16 at 16:42
  • 1
    Are you asking for exactly three pairs of in/out columns in the result of the query, or do you want it to vary depending on how many in/out records there are for each badge? – Mike Christie Dec 27 '16 at 16:47
  • If there are less stamps than 4 the extra field can be empty, what matters is that for each person I can have one record with all stamps rather than multiple ones, through a mysql query – Fabio Facchetti Dec 27 '16 at 16:55

2 Answers2

0

I think you are looking for a Pivot Table. It turns rows into columns.

Here is a tutorial http://www.artfulsoftware.com/infotree/qrytip.php?id=78

Also here is another post on stackoverflow that is very similar MySQL pivot table

Community
  • 1
  • 1
Dillon_Su
  • 91
  • 7
0

Try this.

SELECT Badge, Date, GROUP_CONCAT(TIME ORDER BY TIME SEPARATOR ',') as times, 
GROUP_CONCAT(TYPE ORDER BY TIME SEPARATOR ',') as types
    FROM peoples_hobbies GROUP BY Badge

You can get the result by string manipulation with separator ','.

Star_Man
  • 1,091
  • 1
  • 13
  • 30