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;
pivotoperator is relatively new. If you need to support older versions of Oracle, you'll need to use the oldmax(casetrick. – Justin Cave May 07 '15 at 16:45select * from v$versionwill tell you the version. Are you guaranteed that therankwill never exceed 4? – Justin Cave May 07 '15 at 16:48I'll post an alternate desired output that might be simpler to implement. I've never used pivot before so I dunno, though.
I'm using 11.1
– Daniel Paczuski Bak May 07 '15 at 16:51pivotat least not in a static SQL statement. You could use dynamic SQL to construct the SQL statement at runtime but that would require running a query first to get themax(rank)and then constructing a SQL statement with that number of output columns. Returning a single string would be easier. – Justin Cave May 07 '15 at 16:57I did some research on the error I got and it turns out that LISTAGG isn't available in 11.1. Any idea what I can do instead?
– Daniel Paczuski Bak May 07 '15 at 17:18LISTAGG(). – ypercubeᵀᴹ May 07 '15 at 17:46