Concatenate select in SQL columns

Question

i have 3 tables in n-m relationship. Table A, Table B and Table A_has_B.

SELECT A.ID_Projectos, B.Nombre_Imagen 
FROM Table A_has_B 
INNER JOIN B on Table A_has_B.ID_Imagenes = B.ID_Imagenes 
INNER JOIN A on Table A_has_B.ID_Projectos = A.ID_Projectos

My output with this sql select is:

ID_Projectos | Nombre_Imagen
1            | Name_1.jpg
1            | Name_2.jpg
1            | Name_3.jpg

But i need this output...

ID_Projectos | Nombre_Imagen | Nombre_Imagen2 | Nombre_Imagen3
1            | Name_1.jpg    | Name_2.jpg     | Name_3.jpg

Possible duplicate of [MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table) — Madhur Bhaiya, Sep 21 '18 at 10:12

score 0 · Answer 1 · answered Sep 21 '18 at 10:35

The simplest method is to concatenate the values into a single column:

SELECT A.ID_Projectos, GROUP_CONCAT(B.Nombre_Imagen)
FROM Table A_has_B INNER JOIN
     B
     on Table A_has_B.ID_Imagenes = B.ID_Imagenes INNER JOIN
     A
     on Table A_has_B.ID_Projectos = A.ID_Projectos;

Probably the simplest way to put this in three columns uses string manipulation:

SELECT A.ID_Projectos,
       SUBSTRING_INDEX(GROUP_CONCAT(B.Nombre_Imagen), ',' 1) as imagen_1,
       (CASE WHEN COUNT(*) >= 2 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(B.Nombre_Imagen), ',' 2), ',', -1) END) as imagen_2,
       (CASE WHEN COUNT(*) >= 3 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(B.Nombre_Imagen), ',' 3), ',', -1) END) as imagen_3
FROM Table A_has_B INNER JOIN
     B
     on Table A_has_B.ID_Imagenes = B.ID_Imagenes INNER JOIN
     A
     on Table A_has_B.ID_Projectos = A.ID_Projectos
GROUP BY A.ID_Projectos;

Concatenate select in SQL columns

1 Answers1