CODEWITHSUNDEEP
sqlsql-server
hello
hello

Reputation: 23

SQL query based on column condition

I have a sql table in which below are the columns. I'm using sql server.

Id, name, device and information on if device is active or not.

Need to have a SQL query for below scenario:

I want to display firstdevice, seconddevice columns below based on isActive column. If isACTIVE is 1 then, fill the device name in firstdevice column, if isActive is 0 then fill the device name in secondDevice.

Id name firstDevice secondDevice
1 Motorola phone tablet

Upvotes: 1

Views: 103

Answers (2)

Khan
Khan

Reputation: 16

select id, name, 
MAX(CASE when isActive = 1 then device end) AS firstDevice,
MAX(CASE when isActive = 0 then device end) AS secondDevice
from table
group by 
id, name

Upvotes: 0

Tom J Muthirenthi
Tom J Muthirenthi

Reputation: 3330

I assume you have only maximum 2 rows per id and name

select id, name, 
MAX(DECODE(isActive, 1, device)) AS firstDevice,
MAX(DECODE(isActive, 0, device)) AS secondDevice
from table
group by 
id, name;

Upvotes: 2

Related Questions