How Can I Split A Column Into Few Columns Depending On A Field And Conditions

Question

0.00/5 (No votes)

See more:

am creating a web application based on attendance system. I am using MySQL and PHP. I have a table for every employee that has took attendance and they keep adding when the employees takes attendance on the machine. I extracted the employees that has has attendance 2 times per day which are in and out, and I added them to a table with columns. Now I want to extract the attendances for the employees that has took attendance 1 per day or more than 2 per day which they are error and i want to add them to a table employee time1, time2 and time3.

I have a table as this design, it's an attendance system,
I need to split the time if the attendance count per day was not equal to 2, which are error logs.

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

sorry guys its my first post in my whole entire life , so forgive me if am doing something wrong or not complete

Posted 14-Aug-14 20:10pm

Member 11014259

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jörgen Andersson · Answer 1 · 2014-08-16T09:43:00

On a real database this could have been solved using a rank and a pivot.
But since MySQL doesn't support neither, and also not CTEs (Aargh).
You have to use a construct like this:

SQL

SELECT  
        pd,
        date,
        max(case rank when 1 then time else null end) as time1,
        max(case rank when 2 then time else null end) as time2,
        max(case rank when 3 then time else null end) as time3
FROM
    (
    SELECT  
            pd,
            date,
            time,
            find_in_set
                (
                time,
                    (
                    select  group_concat(time order by time)
                    from    MyTable t2
                    where   t1.pd = t2.pd
                    and t1.date=t2.date
                    )
                ) as rank
    FROM    MyTable t1
    ) as sub
GROUP BY pd,date

Performance will be horrible if your sets are big.