Display Value From Another Row-CodePudding

I have to write a query to display the value for ID2 when ID=ID. Currently the table looks like the below.

ID	ID2	fname	lname	address1	city	state
123	123	joe	smith	12 main st	los angeles	CA
122	122	james	jones	13 main st	new york	NY
123	3210	joe	smith	14 main st	los angeles	CA
124	124	mary	jones	15 main st	new york	NY

The desired output would look like this. Where I can do some sort of a self join to get the ID2 value.

ID	ID2	fname	lname	address1	city	state	other ID
123	123	joe	smith	12 main st	los angeles	CA	3210
122	122	james	jones	13 main st	new york	NY
124	124	mary	jones	15 main st	new york	NY

Any ideas/suggestions greatly appreciated!

CodePudding user response：

You could do something like the following, using a correlated subquery; using max ensures it only returns a single row should you have an Id with more than one different ID2:

select *,
    (select max(id2) from t t2 where t2.id = t.id and t2.id2 != t.id) OtherId
from t
where id = id2

CodePudding user response：

If you have two rows max with the same id, then you can try this :

SELECT (array_agg(t.*) FILTER (WHERE id = id2))[1].*
     , (array_agg(t.id2) FILTER (WHERE id <> id2))[1] AS "other ID"
  FROM your_table AS t
 GROUP BY id

If you may have more than two rows with the same id then you can try this :

SELECT (array_agg(t.*) FILTER (WHERE id = id2))[1].*
     , array_agg(t.id2) FILTER (WHERE id <> id2) AS "other IDs"
  FROM your_table AS t
 GROUP BY id

see the test result in dbfiddle