CODEWITHSUNDEEP
mysqlsql
Terry Babu Samuel
Terry Babu Samuel

Reputation: 119

how to get column name in mysql

this is phpmyadmin table : user_test_detail this is phpmyadmin table : user_test_detail

I want result like below 

(
[1] => option_a
[2] => option_d
[3] => option_e
[4] => option_d
)

Upvotes: 1

Views: 31

Answers (2)

Blank
Blank

Reputation: 12378

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

Upvotes: 1

Mahesh Madushanka
Mahesh Madushanka

Reputation: 3008

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)

Upvotes: 1

Related Questions