5

I am working with postgresql and I have a table like this:

Group | Name
======================================
1     | Mary
2     | Barry,Ann,Peter
3     | Max,Chris
4     | Richard,Mary,Peter,Oliver

The table is an example, you can consider there will be more than 10,000 different names, the max number of names in one group is 4.

I want to sort the name within each group alphabetically so the result would be like this:

Group | Name
======================================
1     | Mary
2     | Ann,Barry,Peter
3     | Chris,Max
4     | Mary,Peter,Oliver,Richard

Thanks

J.YC.Murtaught
  • 51
  • 2
  • 5
    Normalize your data so you're not storing multiple values in a single column, and you can do this with a simple ORDER BY. If you choose to store data in a comma-separated list, expect to have to work extra hard every time you want to use it. The proper way would be to have `2 Barry`, `2 Ann`, `2 Peter` in three separate rows, which would make the query `ORDER BY Group, Name`. – Ken White Mar 01 '17 at 03:29
  • 1
    What is the type of `name`, `text` or `text[]`? – kennytm Mar 01 '17 at 03:36
  • @kennytm the type of name is text – J.YC.Murtaught Mar 01 '17 at 03:39

1 Answers1

1
SELECT t.Group, string_agg(n.names, ',' ORDER BY n.names) AS Name
FROM my_table t,
     regexp_split_to_table(t.Name, ',', 'g') n(names)
GROUP BY 1
ORDER BY 1;
Patrick
  • 29,357
  • 6
  • 62
  • 90