mysql - 如何在 MySQL 中将行显示为列？

Question

伙计们，我的表名为beneficiaryloans，如下所示

+----+----------------+--------+--------+------+--------+-----------+| id | beneficiary_id | hfi_id | amount | rate | period | status_id |+----+----------------+--------+--------+------+--------+-----------+| 15 |             37 |    116 |    123 |  123 |    123 |         4 || 16 |             38 |    117 |    123 |  123 |    123 |         4 || 17 |             39 |    116 |    123 |  123 |    123 |         4 |+----+----------------+--------+--------+------+--------+-----------+

我想根据Hfi_id显示如下

+-----------------+---------------------------+| beneficiary_id  | hfi_id_116 | hfi_id_117   |+-----------------+----------------------------| 37              |  True      |    False     || 38              |  False     |    True      || 39              |  True      |    False     |+----------------------------------------------

如何在 MySQL 中执行此操作？

注意:这里我贴了一些行，hfi_ids一直在这个表里盖章，如果3 hfi_id那么我需要3 列，如果有 4 个 hfi_ids 那么我需要 4 列 等等

Answer 1

试试这个:

SELECT beneficiary_id, 
  CASE hfi_id
      WHEN 116 THEN true
      ELSE false
  END AS hfi_id_116,
  CASE hfi_id
      WHEN 117 THEN true
      ELSE false
  END AS hfi_id_117
FROM your_table

或者如果你需要字符串

SELECT beneficiary_id, 
  CASE hfi_id
      WHEN 116 THEN 'True'
      ELSE 'False'
  END AS hfi_id_116,
  CASE hfi_id
      WHEN 117 THEN 'True'
      ELSE 'False'
  END AS hfi_id_117
FROM your_table