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.
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
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
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 |
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.
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 🙂