CODEWITHSUNDEEP
mysqlsqlconcatenationgroup-concat
eric anthony
eric anthony

Reputation: 71

group concat a field based on 2 same data in another field

Given this table,

+----+------------------+-----------+----------+
| id | Doctor name      | firsttime | lasttime |
+----+------------------+-----------+----------+
|  1 | Dr. Abdurahman   | 12:00:00  | 21:00:00 |
|  2 | Dr. Sultan Hamid | 12:00:00  | 21:00:00 |
|  3 | Dr. Susanti      | 12:00:00  | 21:00:00 |
|  4 | Dr. Siparman     | 12:00:00  | 21:00:00 |
|  5 | Dr. Ramah        | 12:00:00  | 21:00:00 |
|  6 | Drs. Susanto     | 13:00:00  | 22:00:00 |
|  7 | Dr. Budiarjo     | 13:00:00  | 22:00:00 |
|  8 | Dr. Antonius     | 13:00:00  | 22:00:00 |
|  9 | Dr. Wahid Bahyu  | 13:00:00  | 22:00:00 |
+----+------------------+-----------+----------+

expected
+----+--------------------------------------------+-----------+----------+
| id | Doctor name                                | firsttime | lasttime |
+----+--------------------------------------------+-----------+----------+
|  1 | Dr. Abdurahman, Dr. Sultan Hamid, etc      | 12:00:00  | 21:00:00 |
|  2 | Drs. Susanto, Dr. Budiarjo, etc            | 13:00:00  | 22:00:00 |
+----+--------------------------------------------+-----------+----------+

what i'm expecting is to select the table based on the first time and last time with group concat on Doctor name. So if there is the same firstdate AND lastdate it will be included in the group concat

Note: the first date and last date is random, ignore the etc, i want it full name of doctor

Upvotes: 2

Views: 58

Answers (2)

BeLikeMike
BeLikeMike

Reputation: 11

Or you can use STRING_AGG() function:

SELECT STRING_AGG(Doctor name, ', ') AS Doctor name, 
       firsttime, 
       lasttime 
FROM table 
GROUP BY firstdate, lastdate;

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32021

use GROUP_CONCAT

SELECT firstdate,lastdate,
    GROUP_CONCAT(doctorname)
FROM
    table group by firstdate,lastdate

Upvotes: 2

Related Questions