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!

Answer 1

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

Answer 2

Although it doesn't answer your question directly, this article might be helpful to you.

Answer 3

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