Splitting Columns

I want to ask a complicated question and I think its advanced a little.

I have a table has this design, its an attendance system. I need to split the time if the attendance count per day was not 2:

id    pd     time        date
1     5      07:05       08/07/2014
2     4      18:02       07/07/2014
3     1      07:05       06/07/2014
4     1      07:06       06/07/2014
5     1      18:00       06/07/2014

I need to add them to a table in the database and to be split in that form with respect the pd and date

id   pd     time1     time2       time3    ....     date
1    5      07:05                                   08/07/2014
2    4      18:02                                   07/07/2014
3    1      07:05     07:06       18:00             06/07/2014

it would be great if you can help me 🙂

From my limited mysql knowledge, I don’t think you can use PIVOT or ROW_NUMBER(), so here’s a more generic suggestion on how to solve your problem:

SELECT MIN(id) AS id, pd,
       MIN((CASE WHEN ordinal=1 THEN time END)) AS time1,
       MIN((CASE WHEN ordinal=2 THEN time END)) AS time2,
       MIN((CASE WHEN ordinal=3 THEN time END)) AS time3,
       ...
       MIN((CASE WHEN ordinal=99 THEN time END)) AS time99
FROM (
      SELECT t.id, t.pd, t.time, t.date,
             (SELECT 1+COUNT(*)
              FROM someTable AS sub
              WHERE sub.pd=t.pd AND
                    sub.date=t.date AND (
                    sub.time<t.time OR
                    sub.time=t.time AND sub.id<t.id)) AS ordinal
      FROM someTable AS t
     ) AS x
GROUP BY pd, date;

As you can see, it contains an ugly self-referencing subquery and hard-coded columns. My recommendation is that you should probably look for ways to solve this problem in the application layer, by returning the table data properly sorted:

SELECT id, pd, date, time
FROM someTable
ORDER BY pd, date, time;

… and then ordering and formatting the columns in the application.