4

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

Unknown Coder
  • 1,510
  • 2
  • 28
  • 56
  • 2
    You are looking for PIVOT. Unfortunately, MySQL does not support this, but there are workarounds. Try searching Stackoverflow for MySQL PIVOT, or look http://stackoverflow.com/questions/649802/how-to-pivot-a-mysql-entity-attribute-value-schema – Konerak Oct 25 '11 at 11:38
  • It is really interesting puzzle to realize what you want. Could you please attach rules how to produce columns – Dewfy Oct 25 '11 at 11:40

1 Answers1

6

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
Marco
  • 56,740
  • 14
  • 129
  • 152
  • Actually It restricts my way, I can't set case for **hfi_id**, if one more hfi_id added as row, i need to convert that into column dynamically... – Unknown Coder Oct 25 '11 at 13:23
  • @ShreekumarS: next time explain better; you asked something, provided an example of what you wanted to have and now you're asking more... do it better next time ;) – Marco Oct 25 '11 at 15:06
  • @ShreekumarS: I tried it again and it works for me. Check my query again ;) – Marco Oct 25 '11 at 16:19
  • @ShreekumarS: I think you're trying with records all having hfi_id=116 – Marco Oct 25 '11 at 16:29
  • hey dude, thanks for your hint, using your query and MySQL functions it's working as i needed. thank you – Unknown Coder Oct 25 '11 at 16:54