select columns based on column values

Question

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

M.Ali · Accepted Answer · 2014-06-14 00:51:30Z

1

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.

answered Jun 14, 2014 at 0:51

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joseph B · Accepted Answer · 2014-06-14 00:51:16Z

1

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

answered Jun 14, 2014 at 0:51

Joseph B

5,7291 gold badge17 silver badges19 bronze badges

2 Comments

Rahul Over a year ago

Can you format the code to remove horizontal scrollbar.

M.Ali Over a year ago

The second condition in your case statement is unnecessary you could have simply used ELSE there. as there are only two columns to compare.

Collectives™ on Stack Overflow

select columns based on column values

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related