postgresqlpostgresql-15

show all distinct field value of few columns in result with comma separated with ordered ascending


From n number of columns in a table, I want to list a few columns with their corresponding values in comma separated with distinct value. By default, if we group by a column, we can use string_agg() for other fields to add values in comma-separated. In past, I have done once using string_agg() with 'group by' & another pg function without 'group by'. But, I am not able to recall For string_agg I am aware of distinct & null handles along with order. If you know the other function, can you also mention how to achieve distinct, null & order if possible.

The requirement is to show filter for a few columns in the listing table

For example table,

enter image description here

Here, output should be

enter image description here


Solution

  • Use order by inside string_agg() if needed. As id is integer so it need to cast as varchar because string_agg() handles string values.

    select
      string_agg(id :: varchar(500), ',') id,
      string_agg(distinct name, ',') name,
      string_agg(distinct city, ',') city
    from employee
    

    Check this url https://dbfiddle.uk/a_RUrcid