asked on

SQL Server Query Column Name

I need to get the column name from an SQL Server DB table named DataReferrals where a particular number resides in a specific row. For example, if the number 22 is in a row, I would need to know the column name where the 22 is. Is there a SELECT statement that can accomplish this?

ASKER CERTIFIED SOLUTION

aesmike

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cmerlo1

ASKER

Looping through worked...I had actually thought about doing it this way, but I was hoping it could be done the other way...thanks!

ripeshshah

Hi cmerlo1

It is possible in SQL, If you know the number of column in table. I make one query which will return column name.

Table:
consider Table1 has three column A, B, C. data in table1 is as follow
id A B C
1 11 22 3 3
2 10 20 22

You have to find out column name where value is 22 in first row

select * from information_schema.columns where ordinal_position in (
select case when A = 22 then 1 else 0 end from table1 where id = 1
union
select case when B = 22 then 2 else 0 end from table1where id = 1
union
select case when C = 22 then 3 else 0 end from table1where id = 1 )
and table_name = 'table1'

You can make this query dynamic in store procedure.

Hope it will work for you.
Devidas Gaikwad
+919892299766

qtrmile69

what about if you want to return the name of the column where you want to find the last non null field from a group of fields

Column = 8 9 10 11 12 13 14 15
value = 12.3 13.5 13.2 20.1 25.1

Column "14" was the last non null

Thanks