0

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;
14
  • 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. May 7, 2015 at 16:45
  • I'm not sure which version it is, but it's definitely a version where pivot is a thing. May 7, 2015 at 16:46
  • select * from v$version will tell you the version. Are you guaranteed that the rank will never exceed 4? May 7, 2015 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 May 7, 2015 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. May 7, 2015 at 17:06

0

Browse other questions tagged or ask your own question.