CODEWITHSUNDEEP
sqlsql-server
RyanB
RyanB

Reputation: 69

Aggregate values in columns ONLY where there is a difference in values, I do not want to aggreate all values necessarily

Where the values of one column match I want to: 1.aggregate the data in the other columns ONLY where there is a different between the two values 2.If the values are the same the take the value

Example data

Name  MiddleName Surname Age 
Ryan   David     Smith   28
Ryan   David     Smith   29
Sean   John       Johnson 37
Sean   John       Johnson 38

Desired result:

Name  MiddleName Surname Age
Ryan    David    Smith   28, 29
Sean    John     Johnson 37, 38

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 i want to aggregate the ages as they are different, but not the middle name as this is the same for both records

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

result which is not what is desired:

Name    MiddleName Surname Age
Ryan    David      Smith   28, 29
Sean    John, John Johnson 37, 38

Upvotes: 0

Views: 57

Answers (2)

Marc Guillot
Marc Guillot

Reputation: 6465

You can initially take all the different names and add them the aggregated middlenames, surnames and ages on subqueries over the distincts middlnames, surnames and ages for each name that we have previously calculated as CTEs.

with middlenames as (
  select distinct name, middlename
  from t
),
surnames as (
  select distinct name, surname
  from t
)
ages as (
  select distinct name, age
  from t
)
select distinct name,
       (select string_agg(middlename, ',') from middlenames m where m.name = t.name) as middlenames,
       (select string_agg(surname, ',') from surnames s where s.name = t.name) as surnames,
       (select string_agg(age, ',') from ages a where a.name = t.name) as ages
from t

It would be much simpler if SQL Server supported string_agg(DISTINCT middlename, ',') but AFAIK we don't know when it will be available, so we need to first calculate the distinct values separately.

I don't know if there is a simpler way to get these same results, but this one should get you the result you asked for.

Upvotes: 1

Shawn
Shawn

Reputation: 52654

Group by all the values you want to be the same:

SELECT name, middlename, surname, string_agg(age, ',')
FROM t
GROUP BY name, middlename, surname;

Upvotes: 1

Related Questions