I have a sql table in which below are the columns. I'm using sql server.
| Id | name | isActive | device |
|---|---|---|---|
| 1 | Motorola | 1 | phone |
| 1 | Motorola | 0 | tablet |
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 |
CodePudding user response：
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;
CodePudding user response：
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