CODEWITHSUNDEEP
sqlamazon-redshift
user8834780
user8834780

Reputation: 1670

Aggregation of multiple rows into one

This is how the data looks like:

   master_id id states
    1         1  NJ, PA, IL
    1         2  IL, NJ, NY, CA
    1         3  MA, DC

I am trying to pull a summary of states at the master_id level (notice the removal of duplication for NJ and IL), ie.:

master_id states
1         NJ, PA, IL, NY, CA, MA, DC

Any suggestions how I can do it? Of course this needs to be dynamic for all such cases, these states are just an example.

Upvotes: 1

Views: 48

Answers (1)

JohnHC
JohnHC

Reputation: 11195

To join them together, you want listagg:

select master_id, listagg(states, ', ') as states
from MyTable
group by master_id;

But first we split them, and to remove those duplicates we use a distinct:

select master_id, listagg(distinct new_state, ', ') as states
from
(
  select master_id, TRIM(SPLIT_PART(states,',', 1)) as new_state
  from mytable
) x1
group by master_id

And I guess you want the ordered? Ok, let's do that:

select master_id, 
       listagg(distinct new_state, ', ')
         within group (order by new_state) as states
from
(
  select master_id, TRIM(SPLIT_PART(states,',', 1)) as new_state
  from mytable
) x1
group by master_id

Upvotes: 1

Related Questions