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;
ypercubeᵀᴹ
  • 97,895
  • 13
  • 214
  • 305
Daniel Paczuski Bak
  • 416
  • 3
  • 8
  • 20
  • 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 07 '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 07 '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 07 '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 07 '15 at 16:51
  • I posted the alt output. – Daniel Paczuski Bak May 07 '15 at 16:54
  • If the number of columns that you wanted isn't known at compile time, you can't use a pivot at 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 the max(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:57
  • My boss said that returning a single string is actually preferable. How would I do that, though? I know that the pivot syntax requires an aggregation function, is there a way to use concatenation as an aggregation? – Daniel Paczuski Bak May 07 '15 at 16:58
  • 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 07 '15 at 17:06
  • Added my attempt at using the solution you posted. – Daniel Paczuski Bak May 07 '15 at 17:09
  • Can you define "not working"? What output are you getting for the sample data you provided (I assume in your sample data, patient b's rank 2 row should be a 0 not a 1-- otherwise I'm not sure why the desired string would be 10 rather than 11). – Justin Cave May 07 '15 at 17:15
  • You were right, I fixed the sample data.

    I 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:18
  • In the question I linked to, there is a link to a number of different string aggregation techniques. Pick one. – Justin Cave May 07 '15 at 17:19
  • You could also upgrade to 11.2, that has LISTAGG(). – ypercubeᵀᴹ May 07 '15 at 17:46
  • I don't want to delete it because I think it has some information that people could use. – Daniel Paczuski Bak May 07 '15 at 18:22

0 Answers0