Current:
Process | Skill |
---|---|
Process A | Skill_1 |
Process A | Skill_2 |
Process A | Skill_3 |
Process B | Skill_1 |
Process B | Skill_2 |
Expected Result:
Process | Skill |
---|---|
Process A | Skill_1, Skill_2, Skill_3 |
Process B | Skill_1, Skill_2 |
Sample SQL:
SELECT p.pid, p.process, p.product_id, p.sr_id, sr.pid, sr.process_id, sr.skill_req, sr.availability FROM p JOIN sr ON p.pid= sr.pid WHERE p.product_id= '20'
Need help how to rearrange my one to many relationship table. Tried using array “$array= array($row[‘skill_req’]) ” but does not recognize the skill_req variable.
Advertisement
Answer
You’re looking for GROUP BY
and GROUP_CONCAT
For your request, start with something like this and see where you can go from there:
SELECT p.pid, p.process, GROUP_CONCAT(sr.skill_req), FROM p JOIN sr ON p.pid= sr.pid WHERE p.product_id= '20' GROUP BY p.process
Note that SQL will always give you a 2-dimensional array, never more, never less. This means that the cells that you visualize as a result of a query cannot contain an array. At best, you’re going to have comma-separated values, which you can turn into an array on the PHP side.