Say I want something against its next row, or the row after that.

This is how I would do it. Using my trusty old friend ‘Row-Number-Partition-Order-By’

–Use CreditDB

— Create the Table..

Create Table [dbo].[CreditTable1](

[CreditID] [int] Null,

[FirstName] [nvarchar](50) Null,

[Surname] [nvarchar](50) Null,

[Reference] [nvarchar](50) Null,

[Amount] [numeric](18, 2) Null

) On [Primary]

— Fill in data for CreditTable1..

Insert dbo.CreditTable1 (CreditID, FirstName, Surname, Reference, Amount) Values (1, ‘Vladimir’, ‘Boss’, ‘PRM667D’, 1015.33)

Insert dbo.CreditTable1 (CreditID, FirstName, Surname, Reference, Amount) Values (2, ‘Johnny’, ‘Facebook’, ‘FDD942L’, 230.05)

Insert dbo.CreditTable1 (CreditID, FirstName, Surname, Reference, Amount) Values (3, ‘Stevie’, ‘Ball’, ‘DBL684K’, 9484.40)

Insert dbo.CreditTable1 (CreditID, FirstName, Surname, Reference, Amount) Values (4, ‘Christine’, ‘Muller’, ‘KNL973E’, 84.68)

Insert dbo.CreditTable1 (CreditID, FirstName, Surname, Reference, Amount) Values (5, ‘Neeve’, ‘BumbleBee’, ‘KWB443C’, 864.20)

Insert dbo.CreditTable1 (CreditID, FirstName, Surname, Reference, Amount) Values (6, ‘Stevie’, ‘Ball’, ‘DBL684K’, 99.64)

Insert dbo.CreditTable1 (CreditID, FirstName, Surname, Reference, Amount) Values (7, ‘Neeve’, ‘BumbleBee’, ‘KWB443C’, 555.56)

Insert dbo.CreditTable1 (CreditID, FirstName, Surname, Reference, Amount) Values (8, ‘Christine’, ‘Muller’, ‘KNL973E’, 8891.23)

Insert dbo.CreditTable1 (CreditID, FirstName, Surname, Reference, Amount) Values (9, ‘Christine’, ‘Muller’, ‘KNL973E’, 556.84)

— Basic data set…

Select

T1.CreditID

,T1.FirstName

,T1.Surname

,T1.Reference

,T1.Amount

,Row_Number() Over(Partition By Reference Order By CreditID Asc) As Row

From

CreditTable1 T1

Order By

T1.FirstName

,T1.Reference

,T1.CreditID

— The first rows..

Select

Tbl1.*

From

(

Select

T1.CreditID

,T1.FirstName

,T1.Surname

,T1.Reference

,T1.Amount

,Row_Number() Over(Partition By Reference Order By CreditID Asc) As Row

From

CreditTable1 T1

) Tbl1

Where

Tbl1.Row = 1

Order By

Tbl1.FirstName

,Tbl1.Reference

,Tbl1.CreditID

— The second rows..

Select

Tbl2.*

From

(

Select

T1.CreditID

,T1.FirstName

,T1.Surname

,T1.Reference

,T1.Amount

,Row_Number() Over(Partition By Reference Order By CreditID Asc) As Row

From

CreditTable1 T1

) Tbl2

Where

Tbl2.Row = 2

Order By

Tbl2.FirstName

,Tbl2.Reference

,Tbl2.CreditID

— Left Outer Join row 1s to row 2s, by Reference..

Select

*

From

(

— The first rows..

Select

Tbl1.*

From

(

Select

T1.CreditID

,T1.FirstName

,T1.Surname

,T1.Reference

,T1.Amount

,Row_Number() Over(Partition By Reference Order By CreditID Asc) As Row

From

CreditTable1 T1

) Tbl1

Where

Tbl1.Row = 1

) Ta

Left Outer Join

(

— The second rows..

Select

Tbl2.*

From

(

Select

T1.CreditID

,T1.FirstName

,T1.Surname

,T1.Reference

,T1.Amount

,Row_Number() Over(Partition By Reference Order By CreditID Asc) As Row

From

CreditTable1 T1

) Tbl2

Where

Tbl2.Row = 2

) Tb On Ta.Reference = Tb.Reference

Order By

Ta.FirstName

,Ta.Reference

,Ta.CreditID