njlqay njlqay - 5 months ago 36
SQL Question

MySQL PivotTable - How to convert dynamic columns to rows?

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

Answer

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