this is phpmyadmin table : user_test_detail
I want result like below
(
[1] => option_a
[2] => option_d
[3] => option_e
[4] => option_d
)
2 Answers
GET IT form information schema
mysql> SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_NAME="TEST";
+-------------+
| COLUMN_NAME |
+-------------+
| user_id |
| NAME |
| value |
+-------------+
3 rows in set (0.00 sec)
Comments
Try this.
select
case
when option_a = '1' then `option_a`
when option_b = '1' then `option_b`
when option_c = '1' then `option_c`
when option_d = '1' then `option_d`
when option_e = '1' then `option_e`
end as colName
from user_test_detail
order by question_id
This is only for this 5 columns in table user_test_detail, and if you will change this table's structure in the future, you have to use dynamic sql.
Edited:
select
max(case when question_id = 1 then colName end) as `1`,
max(case when question_id = 2 then colName end) as `2`,
max(case when question_id = 3 then colName end) as `3`,
max(case when question_id = 4 then colName end) as `4`
from (
select
case
when option_a = '1' then `option_a`
when option_b = '1' then `option_b`
when option_c = '1' then `option_c`
when option_d = '1' then `option_d`
when option_e = '1' then `option_e`
end as colName,
question_id
from user_test_detail
order by question_id
) t
-- group by question_id
6 Comments
Mahesh Madushanka
you can get it form information schema no need to write complex query
Blank
@MaheshMadushanka Did you realize he did not want all the columns in table, but columns with value is 1 and order by
question_id?Mahesh Madushanka
yes aggred it was not mention in the initial question
Terry Babu Samuel
hello 10086, not getting field name in colName. Right now colName value is 1 as per 4 question.
Blank
@TerryBabuSamuel What do you mean about 'not working'?
|
user_test_detail?