Selecting value from multiple rows

Question

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.

You might need to expand on this slightly. Why do you have two users with the same ID? Is it always Rahul as user1 and Vikram as user2, or could this change? — Paddy
– Paddy, Commented Jul 26, 2013 at 5:44
Agree that more details are needed. If it's just two, you could SELECT MAX(value) AS user1, MIN(value) AS user2 FROM <tablename> GROUP BY id. But this is kind of hacky. — Vyassa Baratham
– Vyassa Baratham, Commented Jul 26, 2013 at 5:46
You can take it like this.Actually ID is addressID. I will always get two rows in which one row will have office address of that person and other row will have residence address based on flag in rows. So i just want to select these two address that office address and residence address. — user2486130
– user2486130, Commented Jul 26, 2013 at 5:48

peterm · Accepted Answer · 2013-07-31 05:09:26Z

2

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

edited Jul 31, 2013 at 5:09

answered Jul 26, 2013 at 6:00

peterm

93k16 gold badges157 silver badges162 bronze badges

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

1 Comment

user2486130 Over a year ago

Just for addition if my query returns only one row. Having Res_Address and Ofc_Address as 0 and 0

Collectives™ on Stack Overflow

Selecting value from multiple rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related