Oracle split repeating rows into columns when joining 2 tables

I have 2 tables like this:

table_a

id  col2
1   A
1   B
2   A
2   B
3   B

table_b

id  col1
1    X
2    Y
3    Z

I want to join both the tables such that the resultant contains the repeating row values in col2 of table_a as columns. Each ID will not contain more than 3 table_a.col2 values. In this example, the result table would look like this :

result:

id col1 col2 col3 col 4
1   X    A    B    null
2   Y    A    B    null
3   Z    B    null null

How can I achieve this? I know I can use the listagg() to aggregate all the rows into one column like this :

 select b.id, b.col1,
 listagg(a.col2, '_') within group (order by a.col2) as col2
 from table_b b join
 table_a a
 on b.id = a.id
 group by b.id, b.col1;

This would give me a result like this :

id col1 col2
1   X    A_B
2   Y    A_B
3   Z    B

Answer

You can use conditional aggregation:

select b.id, b.col1,
       max(case when seqnum = 1 then a.col2 end) as col2,
       max(case when seqnum = 2 then a.col2 end) as col3,
       max(case when seqnum = 3 then a.col2 end) as col4
 from table_b b left join
      (select a.*,
              row_number() over (partition by a.id order by a.col2) as seqnum
       from table_a a
      ) a
      on b.id = a.id
 group by b.id, b.col1;