[Solved] RDBMS Splitting a column into many columns with respect to a field

by


The following result, which isn’t an exact match to your request, but it may get you started. It doesn’t attempt to list all the starting times, but does count the number of entries per pd per date so any that are not equal to 2 can be detected easily.

| ID | PD | MIN_TIME | COUNT_TIME | MAX_TIME |                          DATE |
|----|----|----------|------------|----------|-------------------------------|
|  3 |  1 |    07:05 |          3 |    18:00 |   June, 07 2014 00:00:00+0000 |
|  2 |  4 |    18:02 |          1 |    18:02 |   July, 07 2014 00:00:00+0000 |
|  1 |  5 |    07:05 |          1 |    07:05 | August, 07 2014 00:00:00+0000 |

The query for this is:

SELECT
      MIN(id)     AS id
    , pd
    , MIN(time)   AS min_time
    , COUNT(time) AS count_time
    , MAX(time)   AS max_time
    , date
FROM AttendanceTbl
GROUP BY
      pd
    , date
ORDER BY
      pd
    , date

You can review it at this SQLFIDDLE

Suggestions.

  • include table name(s) as well as the sample data.
  • consider using http://sqlfiddle.com to provide a working example for development of the solution

Note:
– I really don’t like using date or time as field names, I know MySQL lets you do it but I can’t recommend it.

5

solved RDBMS Splitting a column into many columns with respect to a field