A unique text value that obtains a unique value for all rows


Assume I have these rows:

ROW 1 apple,watermelon,pineapple

ROW 2 apple,pineapple,orange

ROW 3 apple,blue berry

I want to create single text value which get unique value from all rows.

How do we do it with query?

Expected Result:

apple,watermelon,pineapple,orange,blue berry

Try this:

select array_agg(val) from (
  select distinct unnest(string_to_array(my_column, ',')) val from my_table) x

A breakdown of what's going on:

  • string_to_array() splits the string, using the specified delimiter, into a true array
  • unnest() turns an array into separate rows - one for each element
  • distinct removes duplicate rows
  • array_agg() joins all rows into a single CSV string (typically you would us a group by clause, but no need here as there's only one group)