SQL query to transform a list of numbers into 2 columns

Question

How can I write a query to transform a list of numbers into 2 columns.
e.g. I have a table with an ID column with values (3, 6, 9, 12, 15, 18).

I would like a result that looks like

ID        NEXT_ID
3         6
6         9
9         12
12        15
15        18

Note that this should work for any sequence of id's not necessarily multiples of 3.
I am working on Oracle 11.2

Answer 1

SELECT first.ID, min(second.ID) as NEXT_ID
FROM TABLE_NAME first, TABLE_NAME second
WHERE first.ID < second.ID
GROUP BY first.ID

Answer 2

You can use the Oracle analytical functions for that purpose

SELECT ID, LEAD(ID, 1) OVER (ORDER BY ID) AS NEXTID
FROM TABLE

Answer 3

F.Y.I. This is one of many possible solutions in mysql (might also work for oracle but I wouldn't know).

The script below is basically directly executable and will result in:

Id  Next
=== ====
3   6
6   9
9   12
12  15
15  18

Here's the script.

CREATE TABLE `Numbers` (
    `Id` INT NOT NULL PRIMARY KEY
);

INSERT INTO `Numbers` (`Id`) VALUES (3), (6), (9), (12), (15), (18);

SELECT 
     `Number`.`Id`
    ,`Next`.`Id` AS `Next`
FROM `Numbers` AS `Number`
LEFT JOIN `Numbers` AS `Next` 
    ON  `Next`.`Id` > `Number`.`Id`
GROUP BY
    `Number`.`Id`
HAVING
    `Next`.`Id` = MIN(`Next`.`Id`);