I have a select statement producing the following:
+---------+---------+------+
| Patient | Pattern | Rank |
+---------+---------+------+
| a | 0 | 1 |
| a | 1 | 2 |
| a | 0 | 3 |
| a | 0 | 4 |
| b | 1 | 1 |
| b | 1 | 2 |
| c | 0 | 1 |
+---------+---------+------+
Desired output:
+---------+-----+---+---+---+
| Patient | 1 | 2 | 3 | 4 |
+---------+-----+---+---+---+
| a | 0 | 1 | 0 | 0 |
| b | 1 | 1 | | |
| c | 0 | | | |
+---------+-----+---+---+---+
Alternate desired output:
+---------+----------+
| Patient | Pattern |
+---------+----------+
| a | 0100 |
| b | 11 |
| c | 0 |
+---------+----------+
I know I need to do a pivot, but not much else. I'm using Oracle.
Here's my progress so far:
SELECT PATIENT_ID, LISTAGG(PATTERN, '') WITHIN GROUP (ORDER BY RANK ASC)
FROM
( the query above )
GROUP BY PATIENT_ID;