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
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
You can use the Oracle analytical functions for that purpose
SELECT ID, LEAD(ID, 1) OVER (ORDER BY ID) AS NEXTID
FROM TABLE
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`);
