Combining two rows using sql statement

Question

I have an enquiry about MySQL statement about combining two rows in a table using sql statement. Initially, I have implemented codes to show in the data grid view the employee's shift.

LocationName | StationName | 12:00 - 13:00 | 13:00 - 14:00 | 14:00 - 15:00
T2           | Counter     | Michael Joyce | Michael Joyce | Michael Joyce

As you can see, there were two employees under the same Location and Station. Next step, I'm going to use SQL statement to type out the format and eventually used the SQL statement to reportviewer.

This is the SQL statement that I tried to show to the same format as shown above.

select
  z.LocationName,
  z.StationName,
  a.12001300,
  b.13001400,
  c.14001500
from (SELECT DISTINCT
        LocationName,
        StationName
      FROM satsschema.employeeslot
      where AllocationDate = '10-Aug'
          and LocationName = 'T2 PML'
          and StationName is not null) z
  left outer join (SELECT
                     LocationName,
                     StationName,
                     EmpName         AS '12001300'
                   FROM satsschema.employeeslot
                   WHERE Assigned = true
                       and AllocationDate = '10-Aug'
                       and (EmpTime = '12:00:00' && EmpTime < '13:00:00')) a
    on z.LocationName = a.LocationName
      and z.StationName = a.StationName
  left outer join (SELECT
                     LocationName,
                     StationName,
                     EmpName         AS '13001400'
                   FROM satsschema.employeeslot
                   WHERE Assigned = true
                       and AllocationDate = '10-Aug'
                       and (EmpTime = '13:00:00' && EmpTime < '14:00:00')) b
    on a.LocationName = b.LocationName
      and a.StationName = b.StationName
  left outer join (SELECT
                     LocationName,
                     StationName,
                     EmpName         AS '14001500'
                   FROM satsschema.employeeslot
                   WHERE Assigned = true
                       and AllocationDate = '10-Aug'
                       and (EmpTime = '14:00:00' && EmpTime < '15:00:00')) c
    on b.LocationName = c.LocationName
      and b.StationName = c.StationName

This SQL statement shown above showed the following results that did not match what I wanted. It shows like this:

LocationName | StationName | 12:00 - 13:00 | 13:00 - 14:00 | 14:00 - 15:00
T2           | Counter     | Michael       | Michael       | Michael

As you can see, It only shows one employee in the Location and Station only. How could be done to the SQL statement that I could also see other employees if there were more then one inside the Location and Station. Any opinions?

"As you can see, there were two employees under the same Location and Station" - I can't see that. — Keith Randall, Sep 04 '12 at 01:57
@KeithRandall Its Michael and Joyce. The names were 2 different people. — Philemon, Sep 04 '12 at 02:02
Use GROUP_CONCAT. See http://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field and http://stackoverflow.com/questions/1067428/combine-multiple-child-rows-into-one-row-mysql — walrii, Sep 04 '12 at 02:48

score 1 · Accepted Answer · edited May 23 '17 at 12:27

Use GROUP_CONCAT. See Can I concatenate multiple MySQL rows into one field?

Something along the lines of:

SELECT z.LocationName, z.StationName, Block12.EmpList, Block13.EmpList, Block14.EmpList
FROM satsschema.employeeslot z
LEFT OUTER JOIN
(
    SELECT LocationName, StationName, GROUP_CONCAT(EmpName) AS EmpList
    FROM satsschema.employeeslot
    WHERE Assigned = true
        AND AllocationDate = '10-Aug'
        AND (EmpTime = '12:00:00' && EmpTime < '13:00:00')
    GROUP BY LocationName, StationName
) Block12
ON z.LocationName = Block12.LocationName
    AND z.StationName = Block12.StationName
LEFT OUTER JOIN
(
    SELECT LocationName, StationName, GROUP_CONCAT(EmpName) AS EmpList
    FROM satsschema.employeeslot
    WHERE Assigned = true
        AND AllocationDate = '10-Aug'
        AND (EmpTime = '13:00:00' && EmpTime < '14:00:00')
    GROUP BY LocationName, StationName
) Block13
ON z.LocationName = Block13.LocationName
    AND z.StationName = Block13.StationName
LEFT OUTER JOIN
(
    SELECT LocationName, StationName, GROUP_CONCAT(EmpName) AS EmpList
    FROM satsschema.employeeslot
    WHERE Assigned = true
        AND AllocationDate = '10-Aug'
        AND (EmpTime = '14:00:00' && EmpTime < '15:00:00')
    GROUP BY LocationName, StationName
) Block14
ON z.LocationName = Block14.LocationName
    AND z.StationName = Block14.StationName
WHERE AllocationDate = '10-Aug'
GROUP BY z.LocationName, z.StationName

Combining two rows using sql statement

1 Answers1