MYSQL - Display multiple columns as different values in select and group by

Question

When I run this query,

select deviceId,value from table group by deviceId;

deviceId    value
T01         50
T01         60  
T01         65
T01         32
T02         34
T02         30  
T02         22
T02         15
T03         70
T03         78  
T03         80
T03         89

But I need the output like below,

T01 T02 T03
50  34  70
60  30  78
65  22  80
32  15  89

How to achieve this using mysql query?

Reason for doing this format, I want to write like this data into excel file.

Possible duplicate of [MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table) — Madhur Bhaiya, Sep 28 '18 at 07:13
can you at least explain the logic for the row values in the output being as they are. — P.Salmon, Sep 28 '18 at 07:15
Seriously consider handling issues of data display in application code — Strawberry, Sep 28 '18 at 07:35

Nick · Answer 1 · 2018-09-29T05:43:26.613

This can be done in MySQL, but it is almost certainly more easily done in your application framework. Anyway, if all the deviceId values have the same number of rows in the table (or if the number of values for T01 >= T02 >= T03), this query will give you the result you are looking for, although it should be noted that there can be no real guarantee on the ordering of data in the output. The query assigns artificial row numbers to each of the deviceId values and then joins the sets of values for each deviceId on that row number:

SELECT t01, t02, t03
FROM (SELECT value AS t01, @rn1 := @rn1+1 AS rn
      FROM table1 t1
      JOIN (SELECT @rn1 := 0) rn1
      WHERE deviceId = 'T01'
      ORDER BY rn) t1
LEFT JOIN (SELECT value AS t02, @rn2 := @rn2+1 AS rn
           FROM table1 t2
           JOIN (SELECT @rn2 := 0) rn1
           WHERE deviceId = 'T02'
           ORDER BY rn) t2 ON t2.rn = t1.rn
LEFT JOIN (SELECT value AS t03, @rn3 := @rn3+1 AS rn
           FROM table1 t1
           JOIN (SELECT @rn3 := 0) rn1
           WHERE deviceId = 'T03'
           ORDER BY rn) t3 ON t3.rn = t1.rn

Output:

t01     t02     t03
50      34      70
60      30      78
65      22      80
32      15      89

If T02 has more values than T01, or T03 more than T02, you would have to do a FULL OUTER JOIN over the three subqueries in the above query, which MySQL does not directly support. This question can point you at how you would do that.

SQLFiddle Demo

MYSQL - Display multiple columns as different values in select and group by

1 Answers1