Aggregate values in columns ONLY there is a difference

Question

Where the values of one column match I want to:

aggregate the data in the other columns where there is a different between the two values
If the values are the same the take the value

Example data

Name  Surname Age
Ryan  Smith   28
Ryan  Smith   29
Sean  Johnson 37

Desired result:

Name  Surname Age
Ryan  Smith   28, 29
Sean  Johnson 37

Name ryan appears twice, so want to aggregate the data for the other fields surname and age ONLY where the data is different for the two rows.

Surname is Smith in both rows so no need to aggregate, just want to populate as Smith in one row.

Age is different so want to aggregate the ages for the two rows into one row

Sean Johnson record is unique for all columns so no need to aggregate or amend anything

I have tried string_agg function but this gives the result:

Name  Surname          Age
Ryan  Smith, Smith     28,29
Sean  Johnson           37

It aggregates all fields irrespective of whether the data between the two rows is different or not.

Don't use `string_agg()` on `surname` and you will be all set. `SELECT Name, Surname, String_Agg(age) FROM yourtable GROUP BY Name, Surname;` If you are getting multiples of the same value inside the `string_agg()` output then you can subquery with a `SELECT DISTINCT` first [like this](https://stackoverflow.com/questions/50589064/get-unique-values-using-string-agg-in-sql-server) — JNevill, Oct 03 '19 at 14:49
Possible duplicate of [Get unique values using STRING\_AGG in SQL Server](https://stackoverflow.com/questions/50589064/get-unique-values-using-string-agg-in-sql-server) — JNevill, Oct 03 '19 at 14:50
The only thing is surname may some times have different values, sometimes it will not. When the surname is different the different surnames need to be aggregate, when the surnames are the same the no aggregation is needed — RyanB, Oct 03 '19 at 15:00

score 0 · Accepted Answer · answered Oct 03 '19 at 15:18

0

You can use:

select name, string_agg(distinct surname, ',') as surname, string_agg(age, ',')
from t
group by name;

This assumes that all names are unique -- that seems like a strong assumption for most datasets.

answered Oct 03 '19 at 15:18

Gordon Linoff

1 Answers1