mysql - SQL-将行转换为列

我是sql Pivot的新手。我有一张叫做“设备”的桌子，上面有这样的内容。

+------------------------------+------------------
| device_id    | serial_number | imei            |
+------------------------------+------------------
| A1yLCM4xe9cn | PIE7JHgo8rLwG | 767523638130820 |
| A1yLCM4xe9cn | PIE7JHgo8rLwG | 100509490452499 |
| OJndw9C5X5I4 | 8lZ0YkPHjv5qA | 893567359155395 |
| OJndw9C5X5I4 | 8lZ0YkPHjv5qA | 336707619575525 |
| RIaKTBPEFQlc | CYGKQLSluJIRq | 269331663151346 |

我正试图得到这样一个select查询

+------------------------------+----------------------------------
| device_id    | serial_number | imei1           | imei2         |
+------------------------------+----------------------------------
| A1yLCM4xe9cn | PIE7JHgo8rLwG | 767523638130820 | 100509490452499
| OJndw9C5X5I4 | 8lZ0YkPHjv5qA | 893567359155395 | 336707619575525
| RIaKTBPEFQlc | CYGKQLSluJIRq | 269331663151346 | NULL

我能得到硬编码的值。是否可以将行动态转换为列？

最佳答案

如果每个设备的id/序列号始终有两个imei值：

select device_id, serial_number, min(imei) as imei1, max(imei) as imei2
from tablename
group by device_id, serial_number

如果每个设备有一个或两个imei值/序列号：

select device_id, serial_number, min(imei) as imei1,
       case when count(*) > 1 then max(imei) end as imei2
from tablename
group by device_id, serial_number

关于mysql - SQL-将行转换为列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/35741229/