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)