select values of a column based on values of another column-CodePudding

I have a table:

ID,     color
----    ------
1       red
2       red
3       green
4       green
5       green
6       blue
7       blue

Desired result of the query:

red    1,2
green  3,4,5
blue   6,7

how to write the query in Postgres SQL?

CodePudding user response：

What you are looking for is the array_agg with the optional order by. In the following the inner select builds the list of ordered id for each color, the outer query then orders the overall result by the lowest id value within the colors.

with test( id, color) as 
     ( values (1, 'red')
            , (2, 'red')
            , (3, 'green')
            , (4, 'green')
            , (5, 'green')
            , (6, 'blue')
            , (7, 'blue')
      ) 

 select color, arr as id_list
   from (
          select color, array_agg(id order by id) arr
            from test 
           group by color
        ) sq    
 order by arr[1];