I have a query that makes something like this:
+---------+---------+------+
| Patient | Pattern | Rank |
+---------+---------+------+
| a | 0 | 1 |
| a | 1 | 2 |
| a | 0 | 3 |
| a | 0 | 4 |
| b | 1 | 1 |
| b | 1 | 2 |
| c | 0 | 1 |
+---------+---------+------+
I would like to turn it into something like this:
+---------+----------+
| Patient | Patternstring |
+---------+----------+
| a | 0100 |
| b | 11 |
| c | 0 |
+---------+----------+
However, I'm getting stuff like this:
+---------+----------+
| Patient | Patternstring |
+---------+----------+
| a | 1000 |
| b | 11 |
| c | 0 |
+---------+----------+
The original query is ordered by patient, then rank. The way I'm producing things like the current output table is with this:
SELECT PATIENT_ID, WM_CONCAT(PATTERN) as PATTERNSTRING
FROM
(That query)
GROUP BY PATIENT ID;
I'm guessing this has something to do with the way WM_CONCAT functions. Even though the query that WM_CONCAT is reading would have it concatenate everything in the right order (assuming it was reading top-bottom), it seems to be reading things in more-or-less a random order. Is there any trick I can use to get WM_CONCAT to work properly?
I know that LISTAGG would let me solve this easily, but I am using Oracle 11.1 and upgrading isn't an option.
Best Answer
The research I did made it seem like I need to either write my own aggregation or upgrade Oracle. I ended up doing neither, and exported the query through Tableau, formatting it in a sheet appropriately, then exporting that sheet to excel and making an aggregation column.