Combine Multiple child rows into one row MYSQL without hardcoding or min/max values

Question

I did research the question (Combine Multiple child rows into one row MYSQL), but none of them is the same as mine. I need a solution, but I don't have any conditions. I see the pivot table examples, but they all use conditions or hardcoding, for example see below:

GROUP_CONCAT(if(colID = 1, value, NULL))
OR
Round(Sum(If( Month(o.orderdate)= 1, (d.unitprice*d.quantity)-d.discount, 0 )), 2 ) AS Jan,

They all use either Max/min, concat or hard coding of id values. In my case, The id's are retrieved from a SQL query, based on another id in the where clause. There can be zero images or 10 per advertiser id. Please see the image below of what I am trying to achieve. IF anybody feels it to be a duplicate, please point me to the correct answer?

Image of table and select

Basically, I have 3 tables, that I am joining into 1, with a select query using joins, my MySQL is below:

select a.PK , a.Name, a.Email, i.Image_Name
 FROM `advertiser` a
    LEFT JOIN category  c ON a.PK = c. FK 
    LEFT JOIN images I on a.PK = i.FK
    where c.FK = 1

Now, my result is something like this:

PK      Name        Email               Image_name
31      Sprouts     info@Sprout.co.za   Sprouts.jpg
31      Sprouts     info@Sprout.co.za   Bananas.jpg
31      Sprouts     info@Sprout.co.za   Apples.jpg
31      Sprouts     info@Sprout.co.za   Lemos.jpg

Where what I want is:

PK      Name        Email               Image_name_1        Image_name_2        Image_name_3        Image_name_4
31      Sprouts     info@Sprout.co.za   Sprouts.jpg         Bananas.jpg         Apples.jpg          Lemos.jpg

All the images on one row per PK.

Any help will be greatly appreciated.

The jargon for this kind of task is "pivoting, " for your information. It happens to be a notorious pain in the neck to do in MySQL. — O. Jones, Mar 23 '16 at 12:13
My bad, it is a duplicate of http://stackoverflow.com/questions/7674786/mysql-pivot-table — Martin Verjans, Mar 23 '16 at 12:16
It is not a duplicate Martin. Those examples all use either conditions or hardcoding. — user3639567, Mar 23 '16 at 12:31

score 0 · Answer 1 · answered Mar 23 '16 at 12:18

GROUP_CONCAT() is probably your best bet to get this project done this year. If you have a limit of 10 images, it probably will work fine. It gets snarled up if you try to concatenate hundreds of items.

This query is worth trying.

select a.PK , a.Name, a.Email,
       GROUP_CONCAT(DISTINCT i.Image_Name SEPARATOR '|') Image_Names
  FROM `advertiser` a
  LEFT JOIN category  c ON a.PK = c. FK 
  LEFT JOIN images I on a.PK = i.FK
 where c.FK = 1
 GROUP BY a.PK, a.Name, a.Email

score 0 · Answer 2 · answered Mar 23 '16 at 12:23

If you not must have in separated field you can use GROUP_CONCAT:

select a.PK , a.Name, a.Email, GROUP_CONCAT(i.Image_Name) AS Images
 FROM `advertiser` a
    LEFT JOIN category  c ON a.PK = c. FK 
    LEFT JOIN images I on a.PK = i.FK
    where c.FK = 1
    group by  a.PK;

Zafar Malik · Accepted Answer · 2016-03-25T09:43:18.407

You can use below query-

SELECT a.PK , a.Name, a.Email, 
SUBSTRING_INDEX(GROUP_CONCAT(i.Image_Name),',',1) AS Image_name_1, 
IF((LENGTH(GROUP_CONCAT(i.Image_Name)) - LENGTH(GROUP_CONCAT(i.Image_Name SEPARATOR ''))) < 1,'',SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(i.Image_Name),',',2),',',-1)) AS Image_name_2, 
IF((LENGTH(GROUP_CONCAT(i.Image_Name)) - LENGTH(GROUP_CONCAT(i.Image_Name SEPARATOR ''))) < 2,'',SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(i.Image_Name),',',3),',',-1),',',-1)) AS Image_name_3, 
IF((LENGTH(GROUP_CONCAT(i.Image_Name)) - LENGTH(GROUP_CONCAT(i.Image_Name SEPARATOR ''))) < 3,'',SUBSTRING_INDEX(GROUP_CONCAT(i.Image_Name),',',-1)) AS Image_name_4 
 FROM `advertiser` a
    LEFT JOIN category  c ON a.PK = c. FK 
    LEFT JOIN images I ON a.PK = i.FK
    WHERE c.FK = 1
    GROUP BY a.PK, a.Name,a.Email;

Hi Zafar, thank you very much. I am one step closer now. However, if no multiple rows are found, it inserts the image_name1's value into all the image_name fields. For example: `Name Email Image_name_1 Image_name_2 Image_name_3 Image_name_4` `30 Mime info@mime.co.za Sprouts.jpg Sprouts.jpg Sprouts.jpg ` — user3639567, Mar 23 '16 at 12:43

Combine Multiple child rows into one row MYSQL without hardcoding or min/max values

3 Answers3

Linked