I am trying to merge different rows into one when they have the same id but different column values.
For example :
id colour1 colour2
1 red blue
2 green red
1 red, blue
2 green, red
Please read my Comment first - you shouldn't even think about doing this unless it is ONLY for reporting purposes, and you want to see how this can be done in plain SQL (as opposed to the correct solution, which is to use your reporting tool for this job).
The second format is easiest, especially if you don't care about the order in which the colors appear:
select id, listagg(colour, ', ') within group (order by null) from table1 group by id
order by null means order randomly. If you want to order by something else, use that in
order by with
listagg(). For example, to order the colors alphabetically, you could say
within group (order by colour).
For the first format, you need to have an a priori limit on the number of columns, and how you do it depends on the version of Oracle you are using (which you should always include in every question you post here and on other discussion boards). The concept is called "pivoting"; since version 11, Oracle has an explicit PIVOT operator that you can use.