Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Why can't you use JOIN? –  Jeremy Smyth Dec 21 '12 at 11:17
    
@JeremySmyth mysql does not support JOIN to self on temporary tables. Temporary tables can be referenced only once in a query. –  All Dec 21 '12 at 11:19
    
@All: really? Wow, that's lame –  a_horse_with_no_name Dec 21 '12 at 11:40
add comment

2 Answers

up vote 4 down vote accepted

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 |
share|improve this answer
add comment

Seeing as you have the pair column to group on, you could use it with a GROUP_CONCAT to give you what you want, in conjunction with a hacky IF(,,) in a subquery. We're only referencing the temp table once, so it gets by MySQL's "Can't reopen table" error.

Try this:

SELECT pair, GROUP_CONCAT(key1) AS key_col1, GROUP_CONCAT(key2) AS key_col2,
             GROUP_CONCAT(val1) AS val_col1, GROUP_CONCAT(val2) AS val_col2
FROM (SELECT id,
             IF(type='key',   col1, NULL) key1,
             IF(type='key',   col2, NULL) key2,
             IF(type='value', col1, NULL) val1,
             IF(type='value', col2, NULL) val2,
             pair
      FROM temp) AS a
GROUP BY pair;

This will break quite deliciously if you've more than two rows per pair, but should behave well if you're in the middle of an edit and only have a key or a value for one pair.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.