Transpose data from a mysql table

Question

I have a table in which the data are entered vertically by indicators as in the example below

^ID ^ idc1   ^ idc2 ^   idc3  ^
|1  |    7   |  11  |    5    |
|2  |    8   |  14  |   19    |
|3  |    9   |  22  |    4    |

I have a query that allows me to transpose the data to fruit horizontally. However, this query only gives me the first line and not all 3

^ apples ^ Pears ^ Orange ^
|   7    |   8   |   9    | (idc1)

I would like to have this result

^ apples ^ Pears ^ Orange ^
|    7   |   8   |    9   |   (idc1)
|    11  |   14  |   22   |   (idc2)
|    5   |   19  |    4   |   (idc3)

Here is the query:

SELECT 
    sum( if( id = 1, idc1, 0 ) ) AS Apples,  
    sum( if( id = 2, idc1, 0 ) ) AS Pears, 
    sum( if( id = 3, idc1, 0 ) ) AS Orange
FROM 
    brf_vr
WHERE  idcdate = '2018-11-27'
GROUP BY 
    idcdate;

Does somebody has any idea

THX in advanced

cheers

So, if you want to add a new apple, you'll have to add a new field idc4 in the first table? — Cid, Dec 10 '18 at 13:29
That will become really painfull, when you'll have 50 of each fruits, but 1 banana and 10 000 berries. — Cid, Dec 10 '18 at 13:45
@Cid, i think you are all right. I must change the table structure. Thanks a lot everybody! — achillix, Dec 10 '18 at 14:10

score 1 · Accepted Answer · answered Dec 10 '18 at 14:26

Try this but I don't recommend:

SELECT idcList,
       MAX(CASE WHEN ID = 1 THEN value END) `Apple`,
       MAX(CASE WHEN ID = 2 THEN value END) `Pears`,
       MAX(CASE WHEN ID = 3 THEN value END) `Orange`
from
  (
    SELECT ID,
           idcList,
           CASE idcList
             WHEN 'idc1' THEN idc1
             WHEN 'idc2' THEN idc2
             WHEN 'idc3' THEN idc3
             END value
    FROM mysql_test t
           CROSS JOIN
           (
             SELECT 'idc1' idcList
             UNION ALL
             SELECT 'idc2'
             UNION ALL
             SELECT 'idc3'
           ) c
  ) q
GROUP BY idcList
ORDER BY FIELD(idcList, 'idc1', 'idc2', 'idc3')

If you can review the schema design

Hello Alberto Moro, thank you very much, it works like a charm! chapeau!!! THX and best wishes Achillix — achillix, Dec 10 '18 at 15:33

Transpose data from a mysql table

1 Answers1