Wm_concat in a specific order

oracle

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.