Take the 2-minute tour ×

Hi I have a table with name test. it got 7 columns id , a , b , c , d , e , f. All this columns contains either 1 or 0. Now i want make a query where i can choose only those columns whose value is 1.

Something like this:

select (condition) from test where id = 5;  

because i have a hotel table with 50 columns out of which 11 columns contains either 1 or 0 representing the facilities of the hotel. I want to make a query which just tells what are the facilities of the hotel.

Any help would be great.

share|improve this question
I don't think you've quite grasped how you should really use database queries. –  Ben Sep 17 '12 at 9:39
I know using sql queries we can just select rows but i want to know can we do that even for columns or how people do that in real time scenarios... –  user1529342 Sep 17 '12 at 9:46
Because it returns a data set - that set wouldn't be structurally normal if you had a random set of columns for each row –  Ben Sep 17 '12 at 9:48
can you be bit clear about that.. –  user1529342 Sep 17 '12 at 9:50
Well, a result set from a database is meant to be a defined set of columns, and the data within them for each row. If the columns are different for each row, then it's not a result set, it's just a load of random data. It has no structure, and would be difficult to work with or predict it's behaviour. It'd be no different to doing a single select with different columns for each row in the database. You're meant to work with the data, not the columns –  Ben Sep 17 '12 at 9:52
show 3 more comments

2 Answers

up vote 0 down vote accepted

select id, (a*64)+(b*32)+(c*16)+(d*8)+(e*4)+(f*2)+(g*1) from test

this number you can reverse it to convert to a 7 digit binary code.

examples: 18 = 0010010 , 1000000 = 64

share|improve this answer
i guess its a smart way ... but i dont have any column named (a*64)+(b*32)+(c*16)+(d*8)+(e*4)+(f*2)+(g*1).. or if i m wrong please be more bit clear –  user1529342 Sep 17 '12 at 10:09
no problem, use the name of your columns. if it has a value of 1 it will multiply and aggregate. you can use your 50 columns just keep doubling the multiplicity. you'll need to parse/convert in your application with a method/function. –  hagensoft Sep 17 '12 at 10:17
you could also concatenate strings with the hardcoded column name if the value is 1. –  hagensoft Sep 17 '12 at 10:24
SELECT IF(a=1, 'a ', '') + IF(b=1, 'b ', '') + ... –  hagensoft Sep 17 '12 at 10:29
add comment

using sql you can select rows, NOT columns

If it is that what you want you can bulid your query like this

select id, a, b, c, d -- columns to select
from test  -- table
where (a = 1 or b=1 or c = 1 or d = 1) -- these are the conditions
share|improve this answer
add comment

Your Answer


