0

This question already has an answer here:

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;

marked as duplicate by Justin Cave, RolandoMySQLDBA, Philᵀᴹ, ypercubeᵀᴹ, Max Vernon May 7 '15 at 20:13

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • What version of Oracle? The pivot operator is relatively new. If you need to support older versions of Oracle, you'll need to use the old max(case trick. – Justin Cave May 7 '15 at 16:45
  • I'm not sure which version it is, but it's definitely a version where pivot is a thing. – Daniel Paczuski Bak May 7 '15 at 16:46
  • select * from v$version will tell you the version. Are you guaranteed that the rank will never exceed 4? – Justin Cave May 7 '15 at 16:48
  • No. The maximum rank is probably quite a bit higher than 4 and the script should be able to deal with a new dataset where it's higher or lower. I'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 7 '15 at 16:51
  • 1
    This is a little different - he's ordering them by the thing he's concatenating and I'm ordering them by a separate column. Also, he's doing it on a table and I'm doing it on a select. Regardless, the solution posted there isn't solving the problem for me. – Daniel Paczuski Bak May 7 '15 at 17:06

Browse other questions tagged or ask your own question.