MySQL rows to columns

START TRANSACTION;
USE `rows2cols`;
INSERT INTO `rows2cols`.`product_properties` (`id`, `product_id`, `property_id`, `value`) VALUES (1, 1, 1, '11');
INSERT INTO `rows2cols`.`product_properties` (`id`, `product_id`, `property_id`, `value`) VALUES (2, 1, 2, '22');
INSERT INTO `rows2cols`.`product_properties` (`id`, `product_id`, `property_id`, `value`) VALUES (3, 2, 1, '22');
INSERT INTO `rows2cols`.`product_properties` (`id`, `product_id`, `property_id`, `value`) VALUES (4, 2, 2, '33');
INSERT INTO `rows2cols`.`product_properties` (`id`, `product_id`, `property_id`, `value`) VALUES (5, 3, 1, '33');
INSERT INTO `rows2cols`.`product_properties` (`id`, `product_id`, `property_id`, `value`) VALUES (6, 3, 2, '44');

COMMIT;

Here is tables data:

mysql> use rows2cols;
Database changed
mysql> select * from products;
+----+-------+
| id | name  |
+----+-------+
|  1 | prod1 |
|  2 | prod2 |
|  3 | prod3 |
+----+-------+
3 rows in set (0.00 sec)

mysql> select * from properties;
+----+--------+
| id | name   |
+----+--------+
|  1 | price  |
|  2 | weight |
+----+--------+
2 rows in set (0.00 sec)

mysql> select * from product_properties;
+----+------------+-------------+-------+
| id | product_id | property_id | value |
+----+------------+-------------+-------+
|  1 |          1 |           1 | 11    |
|  2 |          1 |           2 | 22    |
|  3 |          2 |           1 | 22    |
|  4 |          2 |           2 | 33    |
|  5 |          3 |           1 | 33    |
|  6 |          3 |           2 | 44    |
+----+------------+-------------+-------+
6 rows in set (0.00 sec)

Bad way to retrive data:

mysql> SELECT
    ->     products.id,
    ->     products.name,
    ->     properties.name,
    ->     product_properties.value
    ->
    -> FROM products
    -> JOIN product_properties
    -> ON products.id = product_properties.product_id
    -> JOIN properties
    -> ON product_properties.property_id = properties.id;
+----+-------+--------+-------+
| id | name  | name   | value |
+----+-------+--------+-------+
|  1 | prod1 | price  | 11    |
|  1 | prod1 | weight | 22    |
|  2 | prod2 | price  | 22    |
|  2 | prod2 | weight | 33    |
|  3 | prod3 | price  | 33    |
|  3 | prod3 | weight | 44    |
+----+-------+--------+-------+
6 rows in set (0.00 sec)

Good way:

mysql> SELECT
    ->     products.id,
    ->     products.name,
    ->     GROUP_CONCAT(if(properties.name = 'price', value, NULL)) AS 'price',
    ->     GROUP_CONCAT(if(properties.name = 'weight', value, NULL)) AS 'weight'

    ->
    -> FROM products
    -> JOIN product_properties
    -> ON products.id = product_properties.product_id
    -> JOIN properties
    -> ON product_properties.property_id = properties.id
    -> GROUP BY products.id;
+----+-------+-------+--------+
| id | name  | price | weight |
+----+-------+-------+--------+
|  1 | prod1 | 11    | 22     |
|  2 | prod2 | 22    | 33     |
|  3 | prod3 | 33    | 44     |
+----+-------+-------+--------+
3 rows in set (0.00 sec)

So here is SQL:

SELECT
    products.id,
    products.name,
    GROUP_CONCAT(if(properties.name = 'price', value, NULL)) AS 'price',
    GROUP_CONCAT(if(properties.name = 'weight', value, NULL)) AS 'weight'

FROM products
JOIN product_properties
ON products.id = product_properties.product_id
JOIN properties
ON product_properties.property_id = properties.id
GROUP BY products.id