CODEWITHSUNDEEP
sqlsql-server
kneidels
kneidels

Reputation: 914

SQL: Querying column name using value of other field

I have the following 3 fields in the same table:

cell   |    home    |    primary 
----------------------------------------------
 111        2222          cell
 456565     4654564       home

I would like to reach the value of either cell or home based on the value of the primary field.

I tried the following, which obviously does not work... but can't figure out how to convert the value of primary to be understood as a column name:

SELECT 
    (SELECT primary FROM tblstudents WHERE studentid = 39358) 
FROM
    tblstudents WHERE studentid = 39358

Thanks

Upvotes: 1

Views: 71

Answers (4)

Maksim
Maksim

Reputation: 521

Just in case you do not like the CASE selector :

SELECT cell phone FROM  students WHERE PRIMARY='cell'
UNION
SELECT home phone FROM  students WHERE PRIMARY='home'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

The case statement proposed by Sandip is the correct way.

I want to point out that you can put this directly into the table using a computed column:

ALTER TABLE tblstudents add preferred as (CASE [primary] WHEN 'cell' THEN cell ELSE home END);

This has the advantage that any query using the table always uses the same logic for the preferred number. In addition, if one of the field values change, then preferred automatically changes at the same time.

Note that primary is a SQL reserved word (think "primary key"), so it is a very bad choice for a column name. In general, try to avoid using SQL keywords as identifiers.

Upvotes: 1

the.Legend
the.Legend

Reputation: 686

select primary ,
case 
    when primary = 'cell' then cell
    when primary = 'home' then home
END as phone
from tblstudents 
WHERE studentid = 39358

Upvotes: 0

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14669

You can use CASE:

SELECT
CASE primary WHEN 'cell' THEN cell ELSE home END AS ContactNo,
primary
FROM TableName

Upvotes: 3

Related Questions