Concatenating Column Values Based on Order of Column Values

Question

I am trying to figure out a way to group accounts together which have the same people as joint account holders. There is 1 primary person on an account and up to 4 additional joint account holders. Consider the following example:

Account	Primary	Joint1	Joint2	Joint3	Joint4
123	Mark	Tim	Jim	Brian	John
124	Mark	Jim	Tim	Brian	John
134	Jim	Mark	Tim	Brian	John
324	Ann	Shelly	Helen
168	Shelly	Ann	Helen

So in the above:

• Mark, Tim, Jim, Brian and John are all on accounts 123, 124, 134 and I would want that to be one group
• Ann, Shelly and Helen are all on accounts 324 and 168 and would form another group

I was thinking of going about this by just ordering the Primary and the Joint%s by using case statements and then concatenating the order into one string that I could then group on. So in the case of Ann, Shelly and Helen - ordering them alphabetically would give me Ann, Helen and Shelly and I would concatenate these names together to get "AnnHelenShelly" and group by this string. I have started on this but I am finding it to be a bit messy when I get to situations of 3 or 4 Joint holders on an account. For the case of just a primary and one joint, I just do something like:

case when num_joints = 1 then least(primary, joint1) || greatest(primary, joint1) when num_joints = 2 then least(primary, joint1, joint2) || (case when joint1 > least(primary, joint1, joint2) and joint1 < greatest(primary, joint1, joint2) then joint1 when joint2 > least(primary, joint1, joint2) and joint2 < greatest(primary, joint1, joint2) then joint2 else primary end || greatest(primary, joint1, joint2)

But I am wondering if there is a simpler more scalable way to do this so when I get to num_joints = 3 or 4 it isn't too messy. Or perhaps there's another way to group this without having to create string that is composed of the the primary and joint holders in order.

A couple other notes:

• The primary and joint entries have IDs that are numbers and are unique - so I can order by this ID number
• The Joint entries fill up sequentially based on the number of joints on the account - i.e. if there are 3 joints and a primary on the account, primary has a value as well as joint1, joint2 and joint3 and joint4 would be blank.

Answer 1

Use UNPIVOT to convert the columns into rows, then use LISTAGG to aggregate the names alphabetically per account, and then use LISTAGG again to aggregate the accounts per unique list of names.

This solution is still a bit complicated, and isn't significantly simpler than your CASE version, but it does scale well and it would be trivial to add another column to the solution. The example uses LISTAGG, which is nice for displaying the results, but your final production version probably shouldn't use LISTAGG if you're going to process the results in the database.

Sample Schema

create table accounts
(
    account number,
    primary varchar2(100),
    joint1 varchar2(100),
    joint2 varchar2(100),
    joint3 varchar2(100),
    joint4 varchar2(100)
);

insert into accounts
select 123, 'Mark'  ,'Tim'   ,'Jim'  ,'Brian','John' from dual union all
select 124, 'Mark'  ,'Jim'   ,'Tim'  ,'Brian','John' from dual union all
select 134, 'Jim'   ,'Mark'  ,'Tim'  ,'Brian','John' from dual union all
select 324, 'Ann'   ,'Shelly','Helen',''     ,''     from dual union all
select 168, 'Shelly','Ann'   ,'Helen',''     ,''     from dual;

Query

--Accounts per group.
select names, listagg(account, ',') within group (order by account) accounts
from
(
    --Aggregate names into alphabetical list per account.
    select
        account,
        listagg(account_name, ',') within group (order by account_name) names
    from
    (
        --Unpivot columns into rows.
        select account, account_type, account_name
        from accounts
        unpivot (account_name for account_type in
            (primary as 'primary', joint1 as 'join1', joint2 as 'joint2', joint3 as 'joint3', joint4 as 'joint4')
        )
    )
    group by account
)
group by names
order by names;

Results

NAMES                     ACCOUNTS
-----------------------   -----------
Ann,Helen,Shelly          168,324
Brian,Jim,John,Mark,Tim   123,124,134