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

Question

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 1

The following would solve your problem in the first of the two ways that you proposed. Listagg is what you would use to solve it the second of the two ways (as pointed out in the other 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 up to the maximum number of possible colors for a given ID (this solution is not dynamic).

Additionally, this is putting the colors into color1, color2, etc. based on the alphabetical order of the color names. If you prefer a random order, or some other order, you need to change the order by.

Answer 2

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.