Concatenate multiple rows in single rows in MySQL

Question

How can I concatenate all the rows in single rows when I fire SELECT query?

enter image description here

I want O/P like

101 abc CA USA 102 xyz PH UK 103 pqr WDC EU

Any help kindly appreciated. Thanks

user319198 · Accepted Answer · 2012-02-02 17:07:17Z

12

Use conbination of group_concat and concat functions

 SELECT group_concat( concat( id, " ",name," ",city," ",state," " ) SEPARATOR ' ')
 FROM tablename

answered Feb 2, 2012 at 17:07

user319198

Sign up to request clarification or add additional context in comments.

Comments

Iulian Onofrei · Accepted Answer · 2014-06-12 13:00:14Z

12

You'll need GROUP_CONCAT and CONCAT mysql functions and the query should look like this:

SELECT GROUP_CONCAT( CONCAT( id, ' ', name, ' ', city, ' ', state) SEPARATOR ' ')
FROM students
GROUP BY (1)

Or you can use CONCAT_WS instead:

CONCAT_WS(' ', id, name, city, state)

Iulian Onofrei

9,85611 gold badges72 silver badges118 bronze badges

answered Feb 2, 2012 at 17:04

Vyktor

21.1k6 gold badges69 silver badges98 bronze badges

Unfortunately the links to GROUP_CONCAT and CONCAT_WS are broken :-/