CODEWITHSUNDEEP
sqloracle-databasepivot
user1809566
user1809566

Reputation: 185

Transposing SQL table

I have the following tables (tables below are simplification of more complex task):

abc.FirstTable

ID | Name
---------
10 | test

abc.SecondTable

Id | RefId
----------
20 | 10
21 | 10

What I want to have is:

Id | Name | RefId1 | RefId2
---------------------------
10 | test | 20     | 21

I am stuck with:

select t1.id, t1.name, t2.ID from abc.FirstTable t1
left join abc.SecondTable t2 on t2.refid = t1.id;

which produces two rows. Any tips on how to extend this query using joins? Thank you!

Upvotes: 0

Views: 53

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65288

with t as
( 
  select t1.id id1, t1.name, t2.* from abc_FirstTable t1 join abc_SecondTable t2 on t2.refid = t1.id
)
select * from t
pivot
(
 min(id) min_id, max(id) max_id 
 for (refid) in (10)
);

Upvotes: 1

Related Questions