Oracle convert rows into dynamic columns based on one column

Question

What I have

An Oracle table with below data

COL1  COL2
----  ----
1001  110
1001  111
1001  112
2001  210
2001  211

What I want is

1001  110  111  112
2001  210  210

Basically I want all the values in COL2 which match to COL1 on the same line.

How can I achieve this in Oracle? Please note the number of columns should grow based on available matching rows. If that is not possible, then we can consider max 5 values.

Answer 1

The simplest method is to put them in one column using list_agg():

select col1, listagg(col2, ' ') within group (order by col2) as col2s
from t
group by col1;

For separate columns, I would recommend row_number() and conditional aggregation:

select col1,
       max(case when seqnum = 1 then col2 end) as col2_1,
       max(case when seqnum = 2 then col2 end) as col2_2,
       max(case when seqnum = 3 then col2 end) as col2_3
from (select t.*, row_number() over (partition by col1 order by col2) as seqnum
      from t
     ) t
group by col1;