MYSQL: Is it possible to concatenation multiple rows of record into one row

Question

I want to know if there is any option to output multiple rows into a single row.

for example the regular select * from tbl_name will give a list of all records available in the table.

firstname    lastname
----------   ------------
Lepanto      Fernando
Lourdes      Brillianto
Gerald       Siluvai

Preferred output

firstname will have -> Lepanto###Lourdes###Gerald

lastname will have -> Fernando###Brillianto###Siluvai

Can we have some concatenation done to achieve the above.

Search for group_concat() or use any of the questions on the right hand side of your browser. — a_horse_with_no_name, Jun 12 '13 at 13:22
possible duplicate of [Can I concatenate multiple MySQL rows into one field?](http://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field) — a_horse_with_no_name, Jun 12 '13 at 13:22

score 0 · Accepted Answer · answered Jun 12 '13 at 13:21

0

select group_concat(firstname separator '###') as firstnames,
       group_concat(lastname separator '###') as lastnames
from your_table

answered Jun 12 '13 at 13:21

juergen d

That did work. what a shame that i didn't knew that was possible :) – Lepanto Jun 12 '13 at 13:27

score 0 · Answer 2 · answered Jun 12 '13 at 13:23

0

Use:

select GROUP_CONCAT(firstname SEPARATOR "###") as firstname,
       GROUP_CONCAT(lastname SEPARATOR "###") as lastname
from tblname

answered Jun 12 '13 at 13:23

Denis de Bernardy

2 Answers2