Mysql Query

Question

Hi,

I want to fetch all the records in one line with First Name LastName , First Name Last Name, etc........ in mysql Query.

For example,

mytable looks like this:

rec Id        First Name    Last Name
1           Gnaniyar       Zubair
2           Frankyn        Albert
3           John           Mathew
4           Suhail        Ahmed

I want to fetch all these record sin one line: Output should be like this:

Gnaniyar Zubair , Frankyn Albert, John Mathew, Suhail Ahmed

How to get like this?

• Gnaniyar Zubair

Answer 1

It is not a matter of getting one row with all the records, but a matter of representation of data. Therefore, I suggest to take a simple SELECT query, take the records you need, then arrange them in the view layer as you like.

On the other hand, why do you need to solve this record concatenation at SQL level and not on view level?

Answer 2

If you wanted to get them in just one row, you're probably not using your database properly.

If you just want to join together the first and last names, that's easy:

 SELECT CONCAT(`First Name`, ' ', `Last Name`) FROM mytable

Answer 3

If this must the done in the query, you can use GROUP_CONCAT, but unless you're grouping by something it's a pretty silly query and the concatenation should really be done on the client.

SELECT GROUP_CONCAT(FirstName + ' ' + LastName
                    ORDER BY FirstName, LastName
                    SEPARATOR ', ') AS Names
FROM People;