use listagg to convert columns into rows

SELECT id, LISTAGG(str, ',') WITHIN GROUP (ORDER BY str) AS name

FROM new_test

GROUP BY id;

gives you

1 a,b,c

2 d,e

3 f

from

1 a

1 b

1 c

2 d

2 e

3 f