Aggregating Data from Multiple Columns on Multiple Rows

Toad Data Point & Toad Intelligence Central
1

I’ve got a situation where I have data that, due to the design of the table, is scattered across not only multiple rows, but also multiple columns within those rows.

I’ve attached a text file to try and provide an illustrative example of what I’m working with - it’s not exactly the same, but hopefully it’ll give you an idea.

The primary key on this table is made up of 5 columns, four make up the “account id” and the 5th makes up the “location id”. Not sure if that’srelevant, but there you have it.

What I need to do is find some way of consolidating or aggregating all the relevant “prb” and “fix” codes for each instance of the "account id"into one row that I can include on my output.

I’d prefer not to have duplicate values for the problem or fix on the output, but if there’s no way around that then it’s no big deal - my users will have to live with it.

I cannot use any custom PL/SQL functions because there is no writable userspace. I’ve looked at LISTAGG, but it seems to only work well for asingle column on multiple rows, and if there’s a way to use it on multiple columns on multiple rows, I’ve not figured it out.

I appreciate any suggestions, help, or advice you can give me.
Data & Output Example - Multiple Columns on Multiple Rows.txt (522 Bytes)

2

You’re almost there. Since LISTAGG works well on a single column with multiple rows, then you need to change your table from multiple columns on multiple rows to one column on multiple rows.
i.e.
select account_id, prb
from
(
select account_id, prb1 as prb from mytable
union all
select account_id, prb2 as prb from mytable
union all
select account_id, prb3 as prb from mytable
…
)

Included below is a SQL statement that should do something close to what you want. In any case, I tried it on the sample data you attached to your message and I got the output you describe.

select c.account_id, c.prb_list, f.fix_list
from
(select b.account_id,
listagg (to_char (b.prb), ‘;’)
within group (order by b.prb) over (partition by b.account_id)
as prb_list,
row_number() over (partition by b.account_id order by b.account_id) as rn
from
(select distinct a.account_id, a.prb
from
(select account_id, prb1 as prb from MY_TABLE_NAME_HERE
union all
select account_id, prb2 as prb from MY_TABLE_NAME_HERE
union all
select account_id, prb3 as prb from MY_TABLE_NAME_HERE
union all
select account_id, prb4 as prb from MY_TABLE_NAME_HERE
union all
select account_id, prb5 as prb from MY_TABLE_NAME_HERE
union all
select account_id, prb6 as prb from MY_TABLE_NAME_HERE
union all
select account_id, prb7 as prb from MY_TABLE_NAME_HERE
union all
select account_id, prb8 as prb from MY_TABLE_NAME_HERE
) a
order by a.account_id, a.prb
) b
) c,
(select e.account_id,
listagg (to_char (e.fix), ‘;’)
within group (order by e.fix) over (partition by e.account_id)
as fix_list,
row_number() over (partition by e.account_id order by e.account_id) as rn
from
(select distinct d.account_id, d.fix
from
(select account_id, fix1 as fix from MY_TABLE_NAME_HERE
union all
select account_id, fix2 as fix from MY_TABLE_NAME_HERE
union all
select account_id, fix3 as fix from MY_TABLE_NAME_HERE
union all
select account_id, fix4 as fix from MY_TABLE_NAME_HERE
union all
select account_id, fix5 as fix from MY_TABLE_NAME_HERE
union all
select account_id, fix6 as fix from MY_TABLE_NAME_HERE
) d
order by d.account_id, d.fix
) e
) f
where c.account_id = f.account_id and c.rn = 1 and f.rn = 1 ;

3

By the way, in my post I am doing a
select distinct …
from
(select …
union all
select …
union all
…
)

you could obviously rewrite that to remove the distinct, and use union instead of union all

select
from
(select …
union
select …
union
…
)

I had the union all in there because I was building my query in steps and making sure each individual part returned what was expected.

4

Jacquesrk - I appreciate your help!

I haven’t had a chance to implement this yet - something else came up - but I’m going to try it tonight and see if it works. I’m trying to figure out how to implement it since on the actual data source the unique “account_id” is really 4 different columns that make up the primary key on the table. I’m thinking I can probably just concatenate them together though.

5

Easy enough. Suppose you have two fields making up the account_id, ACCOUNT_ID1 and ACCOUNT_ID2.

Take my example, and everywhere you see ACCOUNT_ID, change it to ACCOUNT_ID1, ACCOUNT_ID2.

select c.account_id1, c.account_id2, c.prb_list, f.fix_list
from
(select b.account_id1, b.account_id2,
listagg (to_char (b.prb), ‘;’)
within group (order by b.prb) over (partition by b.account_id1, b.account_id2)
as prb_list,
row_number() over (partition by b.account_id1, b.account_id2
order by b.account_id1, b.account_id2) as rn
from
(select distinct a.account_id1, a.account_id2, a.prb
from
(select account_id1, account_id2, prb1 as prb from MY_TABLE_NAME_HERE
union all
select account_id1, account_id2, prb2 as prb from MY_TABLE_NAME_HERE
union all
select account_id1, account_id2, prb3 as prb from MY_TABLE_NAME_HERE
union all
select account_id1, account_id2, prb4 as prb from MY_TABLE_NAME_HERE
union all
select account_id1, account_id2, prb5 as prb from MY_TABLE_NAME_HERE
union all
select account_id1, account_id2, prb6 as prb from MY_TABLE_NAME_HERE
union all
select account_id1, account_id2, prb7 as prb from MY_TABLE_NAME_HERE
union all
select account_id1, account_id2, prb8 as prb from MY_TABLE_NAME_HERE
) a
order by a.account_id1, a.account_id2, a.prb
) b
) c,
(select e.account_id1, e.account_id2,
listagg (to_char (e.fix), ‘;’)
within group (order by e.fix) over (partition by e.account_id1, e.account_id2)
as fix_list,
row_number() over (partition by e.account_id1, e.account_id2
order by e.account_id1, e.account_id2) as rn
from
(select distinct d.account_id1, d.account_id2, d.fix
from
(select account_id1, account_id2, fix1 as fix from MY_TABLE_NAME_HERE
union all
select account_id1, account_id2, fix2 as fix from MY_TABLE_NAME_HERE
union all
selectaccount_id1, account_id2, fix3 as fix from MY_TABLE_NAME_HERE
union all
select account_id1, account_id2, fix4 as fix from MY_TABLE_NAME_HERE
union all
select account_id1, account_id2, fix5 as fix from MY_TABLE_NAME_HERE
union all
select account_id1, account_id2, fix6 as fix from MY_TABLE_NAME_HERE
) d
order by d.account_id1, d.account_id2, d.fix
) e
) f
where c.account_id1 = f.account_id1
and c.account_id2 = f.account_id2
and c.rn = 1 and f.rn = 1 ;

Message was edited by: jacquesrk

6

Hate to drag this back up, but I ran into some questions when trying to implement this method today.

If there is a need to join additional tables, or specify filtering via a WHERE clause, where (no pun intended) should that be specified? Within each group that is joined together via the UNION ALL ?

To clarify, our work order information is broken up into multiple tables due to volume and for organization reasons, and so the table that has the problem and fix information just has that information - if I need information on the date the order was created, etc. I have to join to a different work order table.

7

It’s hard to say without knowing exactly how your tables are arranged. My first guess would be that you take the whole select statement that you have from above, ad join to that.
Something like

SELECT *
FROM
(
select c.account_id1, c.account_id2, c.prb_list, f.fix_list
from
(select b.account_id1, b.account_id2,
listagg (to_char (b.prb), ‘;’)
within group (order by b.prb) over (partition by b.account_id1, b.account_id2)
as prb_list,
row_number() over (partition by b.account_id1, b.account_id2
order by b.account_id1, b.account_id2) as rn
from
(select distinct a.account_id1, a.account_id2, a.prb
from
(select account_id1, account_id2, prb1 as prb from MY_TABLE_NAME_HERE
union all
select account_id1, account_id2, prb2 as prb from MY_TABLE_NAME_HERE
union all
select account_id1, account_id2, prb3 as prb from MY_TABLE_NAME_HERE
union all
select account_id1, account_id2, prb4 as prb from MY_TABLE_NAME_HERE
union all
select account_id1, account_id2, prb5 as prb from MY_TABLE_NAME_HERE
union all
select account_id1, account_id2, prb6 as prb from MY_TABLE_NAME_HERE
union all
select account_id1, account_id2, prb7 as prb from MY_TABLE_NAME_HERE
union all
select account_id1, account_id2, prb8 as prb from MY_TABLE_NAME_HERE
) a
order by a.account_id1, a.account_id2, a.prb
) b
) c,
(select e.account_id1, e.account_id2,
listagg (to_char (e.fix), ‘;’)
within group (order by e.fix) over (partition by e.account_id1, e.account_id2)
as fix_list,
row_number() over (partition by e.account_id1, e.account_id2
order by e.account_id1, e.account_id2) as rn
from
(select distinct d.account_id1, d.account_id2, d.fix
from
(select account_id1, account_id2, fix1 as fix from MY_TABLE_NAME_HERE
union all
select account_id1, account_id2, fix2 as fix from MY_TABLE_NAME_HERE
union all
selectaccount_id1, account_id2, fix3 as fix from MY_TABLE_NAME_HERE
union all
select account_id1, account_id2, fix4 as fix from MY_TABLE_NAME_HERE
union all
select account_id1, account_id2, fix5 as fix from MY_TABLE_NAME_HERE
union all
select account_id1, account_id2, fix6 as fix from MY_TABLE_NAME_HERE
) d
order by d.account_id1, d.account_id2, d.fix
) e
) f
where c.account_id1 = f.account_id1
and c.account_id2 = f.account_id2
and c.rn = 1 and f.rn = 1
) DATASET1 D1
INNER JOIN TABLE2 T2
ON (D1.COLUMN_A = T2.COLUMN_B)
WHERE T2.COLUMN_C = SOMETHING ;

8

Understood, let’s see if I can make this a bit more or less complicated :slight_smile:

Table 1: This is the main workorder table. It contains data such as Market, Account, Customer, Order ID, Entry Date, Schedule Date, Work Date, Completed Date, Order Status and a bunch of other fields. The only truly relevant ones (for joining) are Market, Account, Customer, and Order ID.

Table 2: This is the table with the problem & fix information. 1 Row per location per customer per order (it’s massive). Fields in common with Table 1 are Market, Account, Customer, and Order ID.

It’s really not advisable to return all rows from Table 2 and then join - there are 15 Million rows on that table and growing - so if there is a way around doing that, that would probably be ideal.