CODEWITHSUNDEEP
postgresqlstring-aggregation
Yogus
Yogus

Reputation: 2272

How to aggregate all the resulted rows column data in one column?

I have a case driven query . Below is the simplest form

select Column 1 from mytable

Results :

    Column 1        
   latinnametest
   LatinManual
   LatinAuto

Is it possible to show the aggregated data of column 1 data of all the resulted rows in another Column say column 5 in front of each row with comma separated ?

Expected :

Column 1         Column 2
latinnametest  latinnametest,LatinManual,LatinAuto
LatinManual    latinnametest,LatinManual,LatinAuto
LatinAuto      latinnametest,LatinManual,LatinAuto

I have used array_agg and concat() but it aggregates the same row data in column 2 but not as expected to add all rows column data comma separated . Any help please.

Edit : I have tried the solution mentioned below but I am getting repetitive data in the column . see the screenshot. I have hover the mouse over that last column and see the repetitive data . Any solution to this ? [![enter image description here][1]][1]

Upvotes: 0

Views: 1745

Answers (1)

user330315
user330315

Reputation:

You can use string_agg() as a window function:

select column_1, 
       string_agg(column_1, ',') over () as all_values
from the_table;

Edit, after the scope was changed:

If you need distinct values, use a derived table:

select column_1, 
       string_agg(column_1, ',') over () as all_values
from (
  select distinct column_1
  from the_table
) t;

Alternatively with a common table expression:

with vals as (
  select string_agg(distinct column_1, ',') as all_values
  from the_table 
)
select t.column_1, v.all_values
from the_table t
  cross join vals v

Upvotes: 1

Related Questions