MySQL join row values as column names

Question

I've searched for hours but i didnt find a solution for my problem.

I have a query that results in a 2 column table like:

key            value
str01          color
str03          size
str04          length
str07          width
str08          height
str09          propertyXY

And another table with str01 to str10 as column names.

id    str01    str02    str03    str04    ...
1     blue     NULL     big      123m

Is it possible to retrieve the following with one queue?

id    color    size    length    ...
1     blue     big     123m

(notice that str02 is not present in the first queue result) So essentially i want to dynamically rename the column names of the final output query according to the subquery mentioned above.

It would be great if someone had an idea on how to approach this.

the key/value code block is a representation of that table structure.. — , Jun 13 '18 at 09:46
No, you can't join tables on result of query, only on columns of both tables — Artem Ilchenko, Jun 13 '18 at 09:46
the table looks like the query but with more (not to be used) columns And as seen here it is possible when using subqueries : https://stackoverflow.com/questions/10538539/join-two-select-statement-results — , Jun 13 '18 at 09:48
So basically using two seperate queries and handling the column name "conversion" on the server side? — , Jun 13 '18 at 09:54
Possible duplicate of [Need to Convert Columns into Rows in MYSQL](https://stackoverflow.com/questions/42408465/need-to-convert-columns-into-rows-in-mysql) — Arsalan Akhtar, Jun 13 '18 at 10:46
You cannot do this with a simple `select` query. You will need to use dynamic SQL. — Gordon Linoff, Jun 13 '18 at 10:54
Try and refer to: https://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns — Martin, Jun 13 '18 at 11:06

score -1 · Answer 1 · answered Jun 13 '18 at 09:59

You can convert your column like below, other than not possible.

mysql> select id,
    -> str01 as color,
    -> str02 as property_1,
    -> str03 as size,
    -> str04 as length,
    -> str05 as property_2,
    -> str06 as property_3,
    -> str07 as width,
    -> str08 as height,
    -> str09 as propertyXY,
    -> str10 as property_4
    -> from tx2;
+------+-------+------------+------+--------+------------+------------+-------+--------+------------+------------+
| id   | color | property_1 | size | length | property_2 | property_3 | width | height | propertyXY | property_4 |
+------+-------+------------+------+--------+------------+------------+-------+--------+------------+------------+
|    1 | blue  | NULL       | big  | 123m   | NULL       | NULL       | NULL  | NULL   | NULL       | NULL       |
+------+-------+------------+------+--------+------------+------------+-------+--------+------------+------------+
1 row in set (0.00 sec)

Although this renames the columns, it does not rename the columns according to the subquery. — Gordon Linoff, Jun 13 '18 at 10:53

MySQL join row values as column names

1 Answers1