CODEWITHSUNDEEP
sqlpostgresql
Hamza Khalid
Hamza Khalid

Reputation: 241

How to string_agg an array in SQL?

I want to aggregate rows using the string aggregate function, however one column is an array.

I have three columns: Employee, Year, Grades. The column Grade is an array. Example:

Row (1) : Employee: Tom | Year: 2019 | Grades: 11,33
Row (2) : Employee: Tom | Year: 2018 | Grades: 45,24,54

'Grade' column is an array. It represents grades for the different tests he took that year

select 
Employee, 
string_agg (distinct Year, ','),
string_agg (distinct Grades, ',')
from Employee_grades
group by 1

ERROR:

function string_agg(bigint[], unknown) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts.

Upvotes: 2

Views: 11325

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

If you don't really care about removing duplicates, you can convert to a string using array_to_string():

select employee, array_agg(distinct year),
       string_agg(array_to_string(grades, ','), ',')
from (values ('Tom', 2019, array[11, 33]),
             ('Tom', 2018, array[10, 20, 30])
     ) v(employee, year, grades)
group by employee;

If you actually want to remove duplicates, this is a bit trickier. Unnesting and de-duping may be the only approach:

with t as (
      select *
      from (values ('Tom', 2019, array[11, 33]),
                   ('Tom', 2018, array[11, 20, 30])
           ) v(employee, year, grades)
     )
select t.*,
       (select array_agg(distinct grade)
        from t t2 cross join lateral
             unnest(grades) grade
        where t2.employee = t.employee
       ) as grades
from (select employee, array_agg(distinct year) as year
      from t
      group by t.employee
     ) t;

I prefer to leave the results as arrays, but the results can easily be converted to strings if you prefer.

Upvotes: 4

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

You should use casting:

select Employee,
      string_agg(distinct Year::text, ','),
      string_agg(distinct Grades::text, ',') 
from Employee_grades 
group by Employee  -- GROUP BY 1 is a bad practice

Upvotes: 4

Related Questions