Find column name by value

Posted on 2006-05-24
Last Modified: 2008-01-09
Im trying to find the name of a column using a value in that column.  I know it could be ambigious, but I'll have to deal with that.
What I have is a table like:
ID | Input1 | Input2 | Input3 | Input4
1  | front   | back    | top      | side
2  | water | fire      | earth   | air

I have the ID and one of the values in the table, but I need to be able to find the column that that value belongs to.
Is this possible?

Question by:maunded
    LVL 42

    Expert Comment

    Sort of an inverse cross-tab :>).


    Select 'Input1' from yourtable where Input1 = 'YourValue'
    Select 'Input2' from yourtable where Input2 = 'YourValue'
    Select 'Input3' from yourtable where Input3 = 'YourValue'
    Select 'Input4' from yourtable where Input4 = 'YourValue'
    LVL 42

    Accepted Solution

    Or, here's another way, which resolves the potential ambiguity by giving preference to the first match in the case list:

    Select ID
    , Case when Input1 = 'yourvalue' then 'Input1'
                      when Input2 = 'yourvalue' then 'Input2'
                      when Input3 = 'yourvalue' then 'Input3'
                      when Input4 = 'yourvalue' then 'Input4'
    From YourTable
    LVL 1

    Author Comment

    Both very nice, thank you.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now