How to mix pairs of rows without JOIN (in a temporary table)?

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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 |

Method 2

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: ,

Leave a Reply