mysql - SQL - 将行转换为列

Question

我是 sql Pivot 的新手。我有一个名为“设备”的表，其中包含这样的内容。

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

我正在尝试获取这样的选择查询

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

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

Answer 1

如果每个 device_id/serieal_no 总是有两个 imei 值:

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

如果每个 device_id/serieal_no 有一个或两个 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