How to select the field values as column names and rows using mysql

Question

I have one table which contains multiple param name and param value.

I would like to select the records in such a way that all param name should come as header/column name and param values should get as multiple rows.

How to do it in mysql ?

If appropriate, consider handling issues of data display in application code — Strawberry, Mar 08 '19 at 18:50

Fahmi · Answer 1 · 2019-03-08T18:47:12.433

1

use case when expression

select max(case  when param_name='school' then param_value end) as school,
max(case  when param_name='bus' then param_value end) as bus
from tablename group by test2_id

edited Mar 08 '19 at 18:47

answered Mar 08 '19 at 18:22

Fahmi

37,315
5
22
31

I already tried this, and its not working. I am getting 4 rows. one for each case condition – Krish Mar 08 '19 at 18:36
@Krish, I've edited it - you can check now – Fahmi Mar 08 '19 at 18:39
If I use max() it will give only one row but I need all records – Krish Mar 08 '19 at 18:43
@Krish, you can check now – Fahmi Mar 08 '19 at 18:47
How do you know which one's which? – Strawberry Mar 08 '19 at 18:51

score -1 · Answer 2 · answered Mar 08 '19 at 18:28

-1

fa06's answer is probably the best way to make this type of selection if there is a known, static set of param_name values, for which case you can just hardcode them in. If not, you'd probably want to first select unique param_name entries and then a selection of the param_values for each different param_name.

answered Mar 08 '19 at 18:28

Anders Juengst

39
1
1
4

can you please give me the sample query ? – Krish Mar 08 '19 at 18:34

How to select the field values as column names and rows using mysql

2 Answers2