mysql 列显示出来,如何在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

解决方案

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