SQL Merge two rows with the same identifier but with different column values ​​(Oracle)

Question

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

For instance:

(table1) id colour 1 red 1 blue 2 green 2 red 

I would like this to be combined so that the result:

  id colour1 colour2 1 red blue 2 green red 

Or

  id colour 1 red, blue 2 green, red 

Or any other option above so that the strings are somehow concatenated.

Any help would be appreciated! Thanks in advance.

Answer 1

Please read my comment first - you should not even think about it, unless it is intended for reporting purposes, and you want to see how it can be done in simple SQL (as opposed to the correct solution, which use the reporting tool for this tasks).

The second format is easiest, especially if you don’t need 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 random order. If you want to order something else, use this in order by with listagg() . For example, to order the colors in alphabetical order, you can 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 that you post here and further other discussion boards). This concept is called "rotary"; Starting with version 11, Oracle has an explicit PIVOT statement that you can use.

Answer 2

The following solution will solve your problem in the first of two ways that you suggested. Listagg is what you would use to solve this second of two ways (as indicated in another answer):

 select id, min(decode(rn,1,colour,null)) as colour1, min(decode(rn,2,colour,null)) as colour2, min(decode(rn,3,colour,null)) as colour3 from ( select id, colour, row_number() over(partition by id order by colour) as rn from table1 ) group by id; 

In this approach, you need to add additional case statements to the maximum number of possible colors for a given ID (this solution is not dynamic).

In addition, it puts colors in color1, color2, etc., based on the alphabetical order of color names. If you prefer random order or some other order, you need to change order by .