How to mix pairs of rows without JOIN (in a temporary table)? I have a TEMPORARY table containing pairs of key/value as

id   col1      col2        type         pair
1    key1      key2        key          1
2    value1    value2      value        1
3    key3      key4        key          2
4    value3    value4      value        2
5    key5      key6        key          3
6    value5    value6      value        3


I want to mix the key/values as

pair    key_col1     key_col2      value_col1      value_col2
1       key1         key2          value1          value2
2       key3         key4          value3          value4
3       key5         key6          value5          value6


Since, it is a temporary table, I cannot use JOIN. How can I SELECT from this table to have pairs of key/value in each row?
This type of data transformation is known as a PIVOT but unfortunately MySQL does not have a pivot function.  However, this can be replicated using both an aggregate function and a CASE statement:

select pair,
  max(case when type = 'key' then col1 end) Key_col1,
  max(case when type = 'key' then col2 end) Key_col2,
  max(case when type = 'value' then col1 end) value_col1,
  max(case when type = 'value' then col2 end) value_col2
from temp
group by pair


See SQL Fiddle with Demo

The result of the query is:

| PAIR | KEY_COL1 | KEY_COL2 | VALUE_COL1 | VALUE_COL2 |
--------------------------------------------------------
|    1 |     key1 |     key2 |     value1 |     value2 |
|    2 |     key3 |     key4 |     value3 |     value4 |
|    3 |     key5 |     key6 |     value5 |     value6 |