SELECT visitdiag.pcucode, 
GROUP_CONCAT(visitdiag.diagcode) AS group_diagcode, 
visit.visitno, visit.pid, count(*) AS 'จำนวนโรคที่วินิจฉัย', 
GROUP_CONCAT(visit.symptoms) AS 'คำวินิจฉัย', 
visit.weight, visit.height, 
visit.pressure, 
visit.temperature, 
visit.pulse, visit.respri, 
GROUP_CONCAT(visit.diagnote) AS group_diagnote, 
visit.vitalcheck AS 'ผลการตรวจร่างกาย', 
GROUP_CONCAT(DATE_FORMAT(visit.visitdate,'%m-%d-%Y')) AS group_visitdate, 
visit.timeservice, 
visit.timestart,
 GROUP_CONCAT(cdrug.drugname) AS group_drugname, 
visitdrug.unit, 
visitdrug.dose FROM visitdiag 
INNER JOIN visit ON visit.visitno = visitdiag.visitno 
INNER JOIN visitdrug ON visit.visitno = visitdrug.visitno 
INNER JOIN cdrug ON visitdrug.drugcode = cdrug.drugcode 
WHERE visit.visitdate > '2014-12-31' 
AND visit.diagnote IS NOT NULL GROUP BY visit.visitno -- ,visit.visitdate,visit.pid -- visit.pid,visit.visitno,visitdiag.diagcode,visit.visitdate