I would like to transform the following database table from dynamic columns to rows in MySQL:
I already took a deep look at stackoverflow.com as well as at this great example here but none of the cases matches my requirement.
The example shown from the link above would match my requirements if it would be dynamic (I do not know the value 'color' or 'size' because it changes dynamically):
SELECT
item_id,
MAX(IF(property_name = 'color', value, NULL)) AS color,
MAX(IF(property_name = 'size', value, NULL)) AS size,
...
...
...
FROM
properties
GROUP BY
item_id;
So this is my Database Table:
id | customer_id | customer_tbl_id | customer_tbl_col_name
1 | 1 | 1 | CustomerColName_1
2 | 1 | 1 | CustomerColName_2
3 | 1 | 1 | CustomerColName_3
4 | 1 | 2 | CustomerColName_4
5 | 1 | 2 | CustomerColName_5
6 | 2 | 1 | CustomerColName_6
7 | 2 | 1 | CustomerColName_7
Now the result of my SQL query should be like this:
1 (customer_id) | 1 (customer_tbl_id) | CustomerColName_1 | CustomerColName_2 | CustomerColName_3
1 (customer_id) | 2 (customer_tbl_id) | CustomerColName_4 | CustomerColName_5
2 (customer_id) | 1 (customer_tbl_id) | CustomerColName_6 | CustomerColName_7
Use GROUP_CONCAT
SELECT customer_id, customer_tbl_id, GROUP_CONCAT(customer_tbl_col_name) as customer_tbl_col_name
FROM table
GROUP BY customer_id , customer_tbl_id