How to write a query to get different row's column value by comma separated into a single row in php

Question

after executing this query:

`select A.idrequirement , A.title  , A.location , A.companyname,
 A.createdby, B.assignto from " . $_schema . ".requirement A, 
req_assignto_link B where A.createdby = '".$user."' and 
A.idrequirement=B.sourceid order by A.createdon`  ;

I got something like this:

idrequirement title location companyname createdby assignto

25         Android   Banglore  Barclays     mangesh    ninad 
25         Android   Banglore  Barclays     mangesh    mangesh

but I want something like this,

25         | Android | Banglore  | Barclays  | mangesh   | ninad,mangesh

can anyone please tell how to do this?

This is the possible solution [here](http://stackoverflow.com/questions/149772/how-to-use-group-by-to-concatenate-strings-in-mysql) — Yaman Jain, Feb 15 '17 at 08:59

score 2 · Answer 1 · answered Feb 15 '17 at 08:59

2

We can use MySQL's GROUP_CONCAT() for this purpose:

SELECT idrequirement,
       title,
       location,
       companyname,
       createdby,
       GROUP_CONCAT(assignto) AS assignto
FROM yourTable
GROUP BY idrequirement,
         title,
         location,
         companyname,
         createdby

GROUP_CONCAT() will aggregate all values of the column specified as input using CSV format, for each group in the query.

answered Feb 15 '17 at 08:59

Tim Biegeleisen

502,043
27
286
360

Why group by is required for all columns? – Naincy Feb 15 '17 at 09:03

score 0 · Answer 2 · answered Feb 15 '17 at 09:03

0

It will be something like this

"select A.idrequirement , A.title , A.location , A.companyname, A.createdby,
GROUP_CONCAT(B.assignto) AS assignto 
FROM " . $_schema . ".requirement A, req_assignto_link B
WHERE A.createdby = '".$user."' and A.idrequirement=B.sourceid
GROUP BY A.idrequirement ";

answered Feb 15 '17 at 09:03

Naincy

2,953
1
12
21

This query is logically incorrect (though it _might_ run, even successfully, on certain versions of MySQL) because you are selecting non aggregate columns, while grouping only by the `idrequirement`. – Tim Biegeleisen Feb 15 '17 at 09:04
@TimBiegeleisen idrequirement is here an index column and he is joining different table based on that. So, group by on it its not an issue. And usage of group by is dependent on the requirement. What you said is historically correct. – Naincy Feb 15 '17 at 09:14
Your comment makes no sense, and this should not be the accepted answer, but I cannot control this. – Tim Biegeleisen Feb 15 '17 at 09:15
@TimBiegeleisen You can search for it...if you feel my answer is not satisfactory... and share some links....to clarify your point. – Naincy Feb 15 '17 at 09:21

How to write a query to get different row's column value by comma separated into a single row in php

2 Answers2