-1

I have trouble creating what i can read from other threads is called a PIVOT table.

Think the answer is in this article http://stratosprovatopoulos.com/web-development/mysql/pivot-table-with-dynamic-columns/ but cant get my head around it

I have a table of products and a table of images One product can have many images.

If i helps lets say that one product can have up to 8 images.

PRODUCT

+----------+-------------+
|ProductId | ProductName |
+----------+-------------+
|        1 | ProductA    |
+----------+-------------+
|        2 | ProductB    |
+----------|-------------+

IMAGE

+----------+-------------+
|ProductId | ImageName   |
+----------+-------------+
|        1 | FileA       |
+----------+-------------+
|        1 | FileB       |
+----------|-------------+
|        2 | FileC       |
+----------|-------------+

What I have now

SELECT p.ProductId, ProductName, ImageName
FROM PRODUCT p
LEFT JOIN IMAGE i
ON p.ProductId = i.ProductId

+----------+-------------+-----------+
|ProductId | ProductName | ImageName |
+----------+-------------+-----------+
|        1 | ProductA    | FileA     |
+----------+-------------+-----------+
|        1 | ProductA    | FileB     |
+----------+-------------+-----------+
|        2 | ProductB    | FileC     |
+----------+-------------+-----------+

What I need

+----------+-------------+---------+---------+
|ProductId | ProductName | Image1  | Image2  |
+----------+-------------+---------+---------+
|        1 | ProductA    | FileA   | FileB   |
+----------+-------------+---------+---------+
|        2 | ProductB    | FileC   |         |
+----------+-------------+---------+---------+
Michael Falch Madsen
  • 29
  • 2
  • Seriously consider handling issues of data display in application code – Strawberry Feb 12 '18 at 20:35
  • 3
    See if [`GROUP_CONCAT`](https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat) could be an option. – PM 77-1 Feb 12 '18 at 20:43
  • For a pivot to work, you need one of the tables to contain `Image1`, `Image2`, etc. as columns. If you don't have that, you'll need to create it using a query with a user-defined variable that increments, as when creating a Rank. See https://stackoverflow.com/questions/532878/how-to-perform-grouped-ranking-in-mysql for how to do that within each group. – Barmar Feb 12 '18 at 20:47
  • GROUP_CONCAT is near what i need SELECT p.product_id, productName, GROUP_CONCAT(DISTINCT i.filename SEPARATOR ';') AS image FROM product p LEFT JOIN image i ON p.product_id = i.product_id GROUP BY p.product_id – Michael Falch Madsen Feb 12 '18 at 21:07

1 Answers1

0

You cannot do the pivot with the way you display your data. There is no way to give dynamic column names to the pivoted table. As Barmar mentioned you would need to write a ranking function that would display a dynamic value for eact repetition of productid (e.g. 'File 1', 'File 2', 'File 3', etc.). Which is an over complication.

Use PM 77-1 solution and group_concat if you just need to get all the image pathes into a single row. You can always then split it by ','.

magom001
  • 608
  • 6
  • 16