McFixit McFixit - 7 months ago 26
SQL Question

SQL Server - Select pairs of values from one column

Is there a way to select pairs of values sequentially from one column in SQL?

i.e. If i have a table with one column of numbers

SomeID
------
1
2
3
5
7
11


I need to return a set of two columns like so:

FirstID SecondID
-------------------
1 2
2 3
3 5
5 7
7 11


Can this be done?

Edit:

I should've mentioned, the order of the first result set matters, and may not be sequential.

i.e. could be

SomeID
5
3
9
8
...

FirstID SecondID
5 3
3 9
9 8
... ...

Answer
SELECT 
    t1.SomeID as FirstID,
    t2.SomeID as SecondID
FROM
(
    SELECT SomeID, ROW_NUMBER()OVER(ORDER BY SomeID) as Inc
    FROM TABLE
) t1
LEFT JOIN
(
    SELECT SomeID, ROW_NUMBER()OVER(ORDER BY SomeID)-1 as Inc
    FROM TABLE
) t2 ON t2.Inc = t1.Inc

works on sql server >= 2005