How to Display rows as Columns in MySQL?

Question

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

You are looking for PIVOT. Unfortunately, MySQL does not support this, but there are workarounds. Try searching Stackoverflow for MySQL PIVOT, or look stackoverflow.com/questions/649802/… — Konerak
– Konerak, Commented Oct 25, 2011 at 11:38
It is really interesting puzzle to realize what you want. Could you please attach rules how to produce columns — Dewfy
– Dewfy, Commented Oct 25, 2011 at 11:40

Marco · Accepted Answer · 2011-10-25 11:43:44Z

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

edited Oct 25, 2011 at 11:43

answered Oct 25, 2011 at 11:37

Marco

57.7k15 gold badges135 silver badges159 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Unknown Coder Over a year ago

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

Marco Over a year ago

@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 Over a year ago

@ShreekumarS: I tried it again and it works for me. Check my query again ;)

Marco Over a year ago

@ShreekumarS: I think you're trying with records all having hfi_id=116

Unknown Coder Over a year ago

hey dude, thanks for your hint, using your query and MySQL functions it's working as i needed. thank you

Collectives™ on Stack Overflow

How to Display rows as Columns in MySQL?

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related