Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 452
  • Last Modified:

Find column name by value

Hi,
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?
Thanks
D.

0
maunded
Asked:
maunded
  • 2
1 Solution
 
dqmqCommented:
Sort of an inverse cross-tab :>).

Try:

Select 'Input1' from yourtable where Input1 = 'YourValue'
Union
Select 'Input2' from yourtable where Input2 = 'YourValue'
Union
Select 'Input3' from yourtable where Input3 = 'YourValue'
Union
Select 'Input4' from yourtable where Input4 = 'YourValue'
0
 
dqmqCommented:
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'
   End
From YourTable
 
0
 
maundedAuthor Commented:
Both very nice, thank you.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now