CODEWITHSUNDEEP
sqlsql-server
user2486130
user2486130

Reputation: 55

Selecting value from multiple rows

I got two rows from database based on particular id

ID         Address    Res_Address     Office_Address
-------------------------------------------------
4          C 60            0               1
4          D 90            1               0

I just want to select Address having flag res_address 1 in ResAddress And flag having Office_address 1 As OfficeAddress by select statement. I don't want to use stored procedure.

Upvotes: 0

Views: 85

Answers (1)

peterm
peterm

Reputation: 92845

Try

SELECT ID,
       MIN(CASE WHEN Res_Address    = 1 THEN Address END) ResidentAddress,
       MIN(CASE WHEN Office_Address = 1 THEN Address END) OfficeAddress
FROM 
(
  SELECT -- your subquery that returns two rows goes here
) q
 GROUP BY ID

Sample output:

| ID | RESIDENTADDRESS | OFFICEADDRESS |
----------------------------------------
|  4 |            D 90 |          C 60 |

Here is SQLFiddle demo

UPDATE If I understand correctly your comment you can use COALESCE() for that

SELECT ID,
       COALESCE(MIN(CASE WHEN Res_Address = 1    THEN Address END), '0') ResidentAddress,
       COALESCE(MIN(CASE WHEN Office_Address = 1 THEN Address END), '0') OfficeAddress
FROM 
(
  SELECT * FROM Table1
) q
 GROUP BY ID

Here is SQLFiddle demo

Upvotes: 2

Related Questions