Distinct and List of other column in postgresql-CodePudding

I am using postgres DB and i have table with two column name and sal .

   name       Sal
  Raunak     10000
  Raunak     5000
  Rahul      500
  Raunak     300

And i want

Raunak 10000,5000,300
Rahul  500

i am using JPA is there any way to get in JPA data

CodePudding user response：

You can use string_agg function to build a comma separated list of values:

select name, string_agg(sal::text, ',')
from t
group by name

You might want to consider json_agg instead of csv if your application can consume json data.

CodePudding user response：

If you want to preserve the data type of the sal column, you can use array_agg() that returns an array of values. Not sure if JPA will let you access that properly though

select name, array_agg(sal) as sals
from the_table
group by name;

CodePudding user response：

If I understand your question correctly, you want to get the result via below SQL statement:

SELECT
  name,
  string_agg (sal::varchar(22), ', ') as sals
FROM
  test
GROUP BY
  name;

Since it's postgresql related SQL, we can't or hard to express it via common object query. You can construct the above SQL via native query mode in your JPA code.

@Query(value = "<the above query>", nativeQuery = true)
List<Object[]> query();