I have a value (say 5) but I do not know in which column of the table it is stored in. I want to get the column name which has this value. Please help me with this.
This can be achieved with a CASE..WHEN combination, with one WHEN for each column of the table in question. I use a temporary table to demonstrate the technique but it works just as well for normal tables.
drop table #T; create table #T(PK int, c1 int, c2 int, c3 int); insert #T values (1,1,3,5); select PK ,case 5 -- The particular value you're looking for when c1 then 'c1' when c2 then 'c2' when c3 then 'c3' -- one WHEN for each column of the table else 'not found' end from #T;
Most often the CASE is written with the column name given and various values listed. I've inverted this so multiple columns can be checked against a single, known value. This could be achieved just as well using the longer form of CASE i.e.
CASE WHEN C1 = 5 THEN 'C1' WHEN C2 = 5 THEN 'C2' ...
A second option is UNPIVOT, which turns columns into rows. It is well documented in Books Online, though it may take some concentration to follow the examples properly. There are several questions on SO and dba.SE on this topic if you want examples. The output from an UNPIVOT will look something like this:
PK ColName ColValue 1 C1 1 1 C2 3 1 C3 5
You can query this with a simple
WHERE ColValue = 5.
I have difficulty understanding what use you have for this as each column in a table should have different meaning. A value of "5" in the "Age" column is very different to "5" in the "Shoe Size" column. They cannot be meaningfully compared or combined. Perhaps you have a one-to-many relationship hidden in your schema i.e. you have a table with columns named, say, Value1, Value2, Value3 etc. If so I'd suggest you re-examine you design if at all possible.