CODEWITHSUNDEEP
sql-serversql-server-2012
Jushua Norman
Jushua Norman

Reputation: 79

select columns based on column values

i have a table with five column

ID INT, Reading1 INT, Reading2 INT, Status1 INT, Status2 INT

I want to select reading1 if reading1 value is greater than reading2 value and want to select status1 value with it. If reading2 value is greater than reading1 value then I want to select reading2 value and status2 value.

I have tried but it gives me an error:

select ID, 
       case when reading1 > reading2 then reading1 when reading2 > reading1 then reading2 as readings,
       case when reading1 > reading2 then status1 when reading2 > reading1 then status2 as status
from table

my table's data

ID  Reading1    Reading2    Status1 Status2
1   113 88  1   0
2   176 22  2   -1
3   204 39  3   -1
4   99  107 0   1
5   86  103 0   1
6   78  101 0   1
7   100 53  1   0

Upvotes: 0

Views: 45

Answers (2)

M.Ali
M.Ali

Reputation: 69574

Using CASE statement you would do something like this.....

SELECT ID 
       ,CASE WHEN reading1 > reading2 
             THEN reading1 ELSE reading2 END as readings
       ,CASE WHEN reading1 > reading2 
             THEN status1 ELSE status2  END as [status]
FROM table

But since you are on SQL Server 2012 you can make use of new IIF() statement as well.

SELECT ID
      , IIF(Reading1 > Reading2, Reading1, Reading2)   AS [Reading]
      , IIF(Reading1 > Reading2, [Status1], [Status2]) AS [Status]
FROM TABLE

Note

Your conditions does not handle the situation where Reading1 = Reading2 you might want to change you > comparison to >=, At least you will get some results back if both values are equal.

Upvotes: 1

Joseph B
Joseph B

Reputation: 5679

The CASE statement in your query is simply missing END. Try this:

select ID, 
       case when reading1 > reading2 then reading1 when reading2 > reading1 then reading2 end as readings,
       case when reading1 > reading2 then status1 when reading2 > reading1 then status2 end as status
from table;

You have not defined a rule if reading 1 = reading2. You can do this using ELSE, as below:

SELECT ID, 
       CASE WHEN reading1 > reading2 THEN reading1 WHEN reading2 > reading1 THEN reading2 ELSE reading1 END AS readings,
       CASE WHEN reading1 > reading2 THEN status1 WHEN reading2 > reading1 THEN status2 ELSE status1 END AS status
FROM table;

Reference:

CASE (Transact-SQL) on MSDN

Upvotes: 1

Related Questions