2

Hello I have looked on these forums and I need help with getting name of column based on value.

For example I have a table such as:

|Name  | Column1 | Column2 | Column3|
|-----------------------------------|
|Peter | 100     | 120     | 92     |
|-----------------------------------|
|James | 110     | 105     | 88     |
|-----------------------------------|
|David | 90      | 112     | 98     |

I want to do a query of Peter where the value is 92 and I need the query to return Column3.

I know very basic sql and have not really used sqlite to understand it in depth.

I searched and the best I found was this thread which does have a solution that works on mysql: Get column name dynamically by Specific row value.

I tested this and it does exactly what I would like. Of course it's not for sqlite.

I need to know if this can be done using sqlite and if so, what is the proper syntax? Again I know very basic SQL.

If anyone has any tips/advice I appreciate it.

Community
  • 1
  • 1
SmalekTF
  • 97
  • 7
  • How many columns in the table? – peterm Aug 12 '13 at 08:17
  • 1
    What programming language are you using to access SQLite? SQLite has a built-in method to retrieve the column name from a result set column, since it keeps track of the source column at least with simple SELECT and JOIN queries. – Anurag Aug 12 '13 at 20:36
  • I forgot to mention I am developing in C# and using the ADO.NET 2.0 Provider for SQLite(http://sourceforge.net/projects/sqlite-dotnet2/), which I had downloaded a long time ago. If there are better options than the one I am using I'm all ears, but so far it works fine. I guess I'll have to look up this built-in method you mentioned, I was just curious if anyone knew a good method of doing this. Worst case I can retrieve the full result and do in-app coding to do what I want, but would rather do it on SQL end. – SmalekTF Aug 12 '13 at 21:18
  • 1
    Checkout the `ColumnName` method in the source code you linked. It's present in the SQLite3.cs file. It takes a `SQLiteStatement` and the index of the column whose name you want. – Anurag Aug 13 '13 at 07:16

1 Answers1

1

if the columns are few and names fixed you could use this :-

select case when col1 == 92 then col1 
            when col2 == 92 then col2 
            when col3 == 92 then col3
       end
  from tab;
Himanshu
  • 2,384
  • 2
  • 24
  • 42