In a table with multiple keys, how to combine two keys if they have a seperate matching key

Question

I am trying to get some information from two unrelated tables and am using a third table that has a list of a lot of different ids. I want when the primary id matches with another entry for it to recognize that the two separate ids are related.

Using oracle sqldeveloper and am still learning sql, I think I might need to use pivot, but I'm not sure how to carry it out in this situation.

What I have
id1     id2
9PFTXVA DF-2648615
9PFTXVA 0092000000heHY3PHK

What I want
id1     id2        id3
9PFTXVA DF-2648615 0092000000heHY3PHK

https://stackoverflow.com/questions/15674373/pivot-rows-to-columns-without-aggregate — Neville Kuyt, Jun 20 '19 at 15:21

score 0 · Answer 1 · answered Jun 20 '19 at 15:21

0

You can use aggregation:

select id1, max(id2) as id2, max(id2) as id3
from t
group by id1;

answered Jun 20 '19 at 15:21

Gordon Linoff

1,242,037
58
646
786

What could I do if there could be up to 8 different unique IDs in id2 for every 1 id1? This seems like it would only work with the possibility of 2. – mjboezeman Jun 20 '19 at 17:45

In a table with multiple keys, how to combine two keys if they have a seperate matching key

1 Answers1