如何以逗号分隔显示多行数据

Question

I want to fetch data from two different database on basis of where condition In which i am fetching user name, address, pin code ,street address and languages they are spoken. Now problem is i have multiple row data for languages for same user like following.

pid field_languages_spoken_value
 1    English
 1    Spanish
 2    English

Now how I can display them as "English, Spanish".

My code for the query:

SELECT x.field_name_value, 
       x.field_street_address_value, 
       x.field_zip_code_value, 
       x.field_street_address_value, 
       x.field_zip_code_value, 
       x.field_test_tid, 
       x.field_test_tid, 
       x.field_county_value, 
       x.field_county_value, 
       x.field_name_value, 
       x.field_address_value, 
       x.field_county_value, 
       x.mail, 
       x.field_name_value, 
       x.field_languages_spoken_value, 
       x.field_languages_spoken_value, 
       x.field_address_value, 
       x.field_county_value, 
       x.mail 
FROM   (SELECT a.field_name_value, 
               b.field_address_value, 
               c.field_county_value, 
               d.field_test_tid, 
               e.mail, 
               k.field_street_address_value, 
               l.field_zip_code_value, 
               p.uid, 
               Group_concat(Distinct lang.field_languages_spoken_value) 
        FROM   db1.users e 
               INNER JOIN db1.profile AS p 
                       ON p.uid = e.uid 
               INNER JOIN db1.field_data_field_languages_spoken AS lang 
                       ON lang.entity_id = p.pid 
               INNER JOIN db1.field_data_field_name AS a 
                       ON a.entity_id = p.pid 
               INNER JOIN db1.field_data_field_address AS b 
                       ON b.entity_id = p.pid 
               INNER JOIN db1.field_data_field_county AS c 
                       ON c.entity_id = p.pid 
               INNER JOIN db1.field_data_field_street_address AS k 
                       ON k.entity_id = p.pid 
               INNER JOIN db1.field_data_field_zip_code AS l 
                       ON l.entity_id = p.pid 
               INNER JOIN db1.field_data_field_test AS d 
                       ON d.entity_id = p.pid 
        UNION ALL 
        SELECT f.field_name_value, 
               g.field_address_value, 
               h.field_county_value, 
               i.field_test_tid, 
               j.mail, 
               m.field_street_address_value, 
               n.field_zip_code_value, 
               q.uid, 
               Group_concat(Distinct lange.field_languages_spoken_value) 
        FROM   db2.users j 
               INNER JOIN db2.profile AS q 
                       ON q.uid = j.uid 
               INNER JOIN db2.field_data_field_languages_spoken AS lange 
                       ON lange.entity_id = q.pid 
               INNER JOIN db2.field_data_field_name AS f 
                       ON f.entity_id = q.pid 
               INNER JOIN db2.field_data_field_address AS g 
                       ON g.entity_id = q.pid 
               INNER JOIN db2.field_data_field_county AS h 
                       ON h.entity_id = q.pid 
               INNER JOIN db2.field_data_field_street_address AS m 
                       ON m.entity_id = q.pid 
               INNER JOIN db2.field_data_field_zip_code AS n 
                       ON n.entity_id = q.pid 
               INNER JOIN db2.field_data_field_test AS i 
                       ON i.entity_id = q.pid) AS x 
WHERE  x.field_county_value LIKE :city 
       AND x.field_test_tid LIKE :speciality

How do I go about this problem.

Answer 1

Try using Separator inside group concat

GROUP_CONCAT(lange.field_languages_spoken_value SEPARATOR ',')

And group it by uid

Hope the result is same as your expected :)

[EDITED]: Try this query I have modify it hope there is no error in there and make the result as your expected :)

SELECT x.field_name_value, 
   x.field_street_address_value, 
   x.field_zip_code_value, 
   x.field_street_address_value, 
   x.field_zip_code_value, 
   x.field_test_tid, 
   x.field_test_tid, 
   x.field_county_value, 
   x.field_county_value, 
   x.field_name_value, 
   x.field_address_value, 
   x.field_county_value, 
   x.mail, 
   x.field_name_value, 
   x.field_languages_spoken_value, 
   x.field_languages_spoken_value, 
   x.field_address_value, 
   x.field_county_value, 
   x.mail 
FROM   (SELECT a.field_name_value, 
           b.field_address_value, 
           c.field_county_value, 
           d.field_test_tid, 
           e.mail, 
           k.field_street_address_value, 
           l.field_zip_code_value, 
           p.uid, 
           lange.field_languages_spoken_value 
    FROM   db1.users e 
           INNER JOIN db1.profile AS p 
                   ON p.uid = e.uid 
       INNER JOIN (SELECT lange1.entity_id,GROUP_CONCAT(lange1.field_languages_spoken_value SEPARATOR ',') as `field_languages_spoken_value` FROM db1.field_data_field_languages_spoken lange1 GROUP BY lange1.entity_id) AS lange
                   ON lang.entity_id = p.pid 
           INNER JOIN db1.field_data_field_name AS a 
                   ON a.entity_id = p.pid 
           INNER JOIN db1.field_data_field_address AS b 
                   ON b.entity_id = p.pid 
           INNER JOIN db1.field_data_field_county AS c 
                   ON c.entity_id = p.pid 
           INNER JOIN db1.field_data_field_street_address AS k 
                   ON k.entity_id = p.pid 
           INNER JOIN db1.field_data_field_zip_code AS l 
                   ON l.entity_id = p.pid 
           INNER JOIN db1.field_data_field_test AS d 
                   ON d.entity_id = p.pid 
    UNION ALL 
    SELECT f.field_name_value, 
           g.field_address_value, 
           h.field_county_value, 
           i.field_test_tid, 
           j.mail, 
           m.field_street_address_value, 
           n.field_zip_code_value, 
           q.uid, 
           lange.field_languages_spoken_value
    FROM   db2.users j 
           INNER JOIN db2.profile AS q 
                   ON q.uid = j.uid
       INNER JOIN (SELECT lange2.entity_id,GROUP_CONCAT(lange2.field_languages_spoken_value SEPARATOR ',') as `field_languages_spoken_value` FROM db2.field_data_field_languages_spoken lange2 GROUP BY lange2.entity_id) AS lange 
                   ON lange.entity_id = q.pid 
           INNER JOIN db2.field_data_field_name AS f 
                   ON f.entity_id = q.pid 
           INNER JOIN db2.field_data_field_address AS g 
                   ON g.entity_id = q.pid 
           INNER JOIN db2.field_data_field_county AS h 
                   ON h.entity_id = q.pid 
           INNER JOIN db2.field_data_field_street_address AS m 
                   ON m.entity_id = q.pid 
           INNER JOIN db2.field_data_field_zip_code AS n 
                   ON n.entity_id = q.pid 
           INNER JOIN db2.field_data_field_test AS i 
                   ON i.entity_id = q.pid) AS x 
WHERE  x.field_county_value LIKE :city 
AND x.field_test_tid LIKE :speciality