MySQL select from two column table

Question

i have a table with only 2 columns. for example

table product.combinations
Column 1: product.id
Column 2: attribute.id

Product.id is unique every product can have 1 or more attributes. Products without attributes are not in the table

for example

table product.combinations

product.id | attribute.id

  1    |     1      |
  1    |     2      |
  2    |     1      |
  2    |     2      |
  5    |     1      |
  5    |     3      |
  9    |     2      |
  9    |     3      |
  9    |     5      |

Now i would like to do a select with the result below

product.id | attribute1 | attribute2 | attribute3 |attribute 4 | attribute 5

  1    |     1      |    2       |            |            |
  2    |     1      |    2       |            |            |
  5    |     1      |    3       |            |            |
  9    |     2      |    3       |     5      |            |

i have already tried with a pivot but i was not able to hae o good result. Can anyone give me a help?

Answer 1

First: DO NOT use dots/periods in table/column names, this won't work, use underscores instead.

Second, if it is ok to have all attribute ids in a comma-separated list, you can do the following (using MySQL's GROUP_CONCAT function):

mysql> SELECT
    -> product_id, GROUP_CONCAT(attribute_id ORDER BY attribute_id) AS attributes
    -> FROM product_combinations
    -> GROUP BY product_id
    -> ORDER BY product_id;
+------------+---------------+
| product_id | attributes    |
+------------+---------------+
|          1 | 1,2           |
|          2 | 1,2           |
|          5 | 1,3           |
|          9 | 2,3,5         |
+------------+---------------+
4 rows in set (0.00 sec)

Answer 2

SELECT ATTRIBUTE FROM combinations UNION ALL SELECT ATTRIBUTE FROM combinations