Select values from different rows of same column INTO multiple variables in Oracle SQL

Question

Here's the example:

ID | value
1     51
2     25
3     11
4     27
5     21

I need to get first three parameters and place them into variables e.g. out_x, out_y, out_z. Is it possible to do it without multiple selects?

Answer 1

You can do something like this:

select max(case when id = 1 then value end),
       max(case when id = 2 then value end),
       max(case when id = 3 then value end)
into out_x, out_y, out_z
from t
where id in (1, 2, 3);

However, I think three queries of the form:

select value into out_x
from t
where id = 1;

is a cleaner approach.

Answer 2

You can use a PIVOT:

SELECT x, y, z
INTO   out_x, out_y, out_z
FROM   your_table
PIVOT  ( MAX( value ) FOR id IN ( 1 AS x, 2 AS y, 3 AS z ) )

Or, if you do not know which IDs you need (but just want the first 3) then:

SELECT x, y, z
INTO   out_x, out_y, out_z
FROM   (
         SELECT value, ROWNUM AS rn
         FROM ( SELECT value FROM your_table ORDER BY id )
         WHERE  ROWNUM <= 3
       )
PIVOT  ( MAX( value ) FOR rn IN ( 1 AS x, 2 AS y, 3 AS z ) )