MySql from two columns to three columns-CodePudding

can somebody help me with my problem.

I have the following table tw_number

id	value
5	34
6	555
6	434
5	23
5	43
5	45

and want to get somthing like this in MySQL Query

id	Temperature	Power
5	34
6		555
6		434
5	23
5	43
5	45

Thanks a lot!

CodePudding user response：

You can use a Query like this, but you must have a field to oder the output.

SELECT sid
, IF( sid = 5, val,'' ) AS Temperature
, IF( sid = 6, val,'' ) AS POWER
FROM tw_number
ORDER BY id;

you can also use this if you only want sid 5 or 6

SELECT sid
, IF( sid = 5, val,'' ) AS Temperature
, IF( sid = 6, val,'' ) AS POWER
FROM tw_number
WHERE sid IN(5,6)
ORDER BY id;

sample

MariaDB [bernd]> SELECT * FROM tw_number;
 ---- ----- ------ 
| id | sid | val  |
 ---- ----- ------ 
|  1 |   5 |   43 |
|  2 |   6 |   45 |
|  3 |   6 |   45 |
|  4 |   5 |   43 |
|  5 |   5 |   43 |
|  6 |   6 |   45 |
 ---- ----- ------ 
6 rows in set (0.00 sec)

MariaDB [bernd]> SELECT sid
    -> , IF( sid = 5, val,'' ) AS Temperature
    -> , IF( sid = 6, val,'' ) AS POWER
    -> FROM tw_number
    -> ORDER BY id;
 ----- ------------- ------- 
| sid | Temperature | POWER |
 ----- ------------- ------- 
|   5 | 43          |       |
|   6 |             | 45    |
|   6 |             | 45    |
|   5 | 43          |       |
|   5 | 43          |       |
|   6 |             | 45    |
 ----- ------------- ------- 
6 rows in set (0.00 sec)

MariaDB [bernd]>