บทความ > SQL

[MySQL] Join multiple rows into one by GROUP_CONCAT Turn Back

เมื่อ 2017-05-29 11:47:58

 

Example:

System: Request Job (Ticket)

 

Table:

Employee (ID,Name,Surname)

Type (Of request) : (ID, Name)

Approver (Head of each the system,type) : (ID,SYS_ID, TYP_ID, EMP_ID)

 

Query:

Fetch all type and show multiple head employee on one row (Default separate by comma (,) or '\r \n' for new line ).

SELECT t.Name, (

SELECT GROUP_CONCAT(e.Name,' ',e.Surname SEPARATOR '\r \n') FROM Approver a

LEFT JOIN Employee e on e.ID= a.EMP_ID

WHERE a.SYS_ID='__xxx__' AND a.TYP_ID = t.ID 

) APPV1

FROM Type t

 

PHP:

echo nl2br( $row[APPV1] );

 

Turn Back

Automotive ดูทั้งหมด

IT Update ดูทั้งหมด