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.

  • What have you tried so far and what particular errors are you seeing? You can edit your question to add whatever queries you have currently. – Michael Green Feb 15 '15 at 2:25

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);

    ,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'
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.

    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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.