CODEWITHSUNDEEP
mysql
Ridwan Islam
Ridwan Islam

Reputation: 13

How do i show the name of a column as a row data using a sql query?

I am quite inexperienced with the query writing.

I have a table like this,

+----+--------+------------+------------+------------+    
| id | name   | Character1 | Character2 | Character3 |
+----+--------+------------+------------+------------+      
| 1  | A      |     1      |      0     |     0      | 
+----+--------+------------+------------+------------+   
| 2  | B      |     0      |      1     |     0      |
+----+--------+---------------+---------+------------+

i want to make a query where the result would show me like this, where i put a condition to search a name,

+----+--------+---------------+
| id  | name  | Character     |
+----+--------+---------------+
| 1   | A     | Character 1   |
+----+--------+---------------+

What will be the query for this?

Upvotes: 1

Views: 60

Answers (2)

Lawraoke
Lawraoke

Reputation: 556

There you go

select id, name, case 1
   WHEN characters1 = 1 THEN characters1
   WHEN characters2 = 1 THEN characters2
   WHEN YourColumn = 1 THEN YourColumn //if you have more
Else 'no 1' END
    from YourTableName where name LIKE '%searhname%'

Replace YourTableName above as your real table name, meanwhile searchname is what you or user is going to type in and search YourColumn will be character2 and 3... or many more :)

For more information about LIKE, such as the difference between '%name' and 'name%', you may view this link here

Upvotes: 1

forpas
forpas

Reputation: 164064

You need a CASE expression:

select id, name,
  case 1
    when Character1 then 'Character1'
    when Character2 then 'Character2'
    when Character3 then 'Character3'
  end Character
from tablename

Upvotes: 0

Related Questions