How to Display rows as Columns in MySQL?

问题:

Guys I've table called beneficiaryloans as follows

+----+----------------+--------+--------+------+--------+-----------+
| 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 |
+----+----------------+--------+--------+------+--------+-----------+

I want to Display It as follows based on Hfi_id

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

How to do this in MySQL?

Note: Here I posted some of rows, and hfi_ids are keep stamping in this table, if 3 hfi_id then i need 3 columns, if 4 hfi_ids then i need 4 columns and so on

回答1:

Try this:

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

or if you need strings

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