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

Answer

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

​x
 
select id, name, MAX(CASE when isActive = 1 then device end) AS firstDevice,MAX(CASE when isActive = 0 then device end) AS secondDevicefrom tablegroup by id, name​

Advertisement

Answer