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.