Is it possible to take two rows and concatenate them in MySQL?

Question

I have two rows of data and I would like to know if it is possible to put all results in a single row?

1     var1     var2
2     var4     var5

to

1     var1     var2     var4     var5

Thanks!

You could JOIN the table to itself, but you'd have to have a constraint to JOIN on... — Cᴏʀʏ, Aug 17 '09 at 11:52
Dupe: http://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field — karim79, Aug 17 '09 at 11:54
Cory, would you mind giving me a sample of the join? I think this would work and is what I have used once before but I have no idea where I have that code — , Aug 17 '09 at 11:58

score 0 · Accepted Answer · answered Aug 17 '09 at 11:53

0

SELECT  GROUP_CONCAT(CONCAT_WS(' ', col1, col2) SEPARATOR ' ')
FROM    mytable

If you want to make it in 4 columns, use a self join:

SELECT  m1.col1, m1.col2, m2.col1, m2.col2
FROM    mytable m1
CROSS JOIN
        mytable m2
WHERE   m1.id = 1
        AND m2.id = 2

answered Aug 17 '09 at 11:53

Quassnoi

Thanks Quassnoi, I am almost 100$ certain this is what I had a while back that worked for me but I couldn't remember the join type. Thanks! – Aug 17 '09 at 12:01

score 0 · Answer 2 · answered Aug 17 '09 at 12:03

I don't know about any mySQL specifics but plain in SQL you could use some thing like that:

SELECT t1.col2, t1.col3, t2.col2, t2.col3 
FROM table as t1, table as t2 
WHERE t1.id = 1 AND t2.id = 2;

were your table looks like:

table:
id    col2     col3
====================
1     var1     var2
2     var4     var5

This is the same as a join statement involving the same table on both sides

2 Answers2