LEJ LEJ - 10 days ago 6
SQL Question

SQL Merge two rows with same ID but different column values (Oracle)

I am trying to merge different rows into one when they have the same id but different column values.

For example :

(table1)

id colour

1 red
1 blue
2 green
2 red


I would like this to be combine so that the result is :

id colour1 colour2

1 red blue
2 green red


Or

id colour

1 red, blue
2 green, red


Or any other variation of the above so that the rows are joined together some way.

Any help would be appreciated! Thanks in advance.

Answer

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.