jessica.green2312 (11/15/2013)

---

Hi everyone,

I'm not even sure how to word this succinctly enough to search for this topic, so I apologize in advance if this is covered elsewhere! It almost feels like a gaps and islands issue, but not quite....

Background Info:

I am developing a form for a mortgage company. There can be any number of borrowers on a given loan, and the business has asked that this form return only 2 borrowers at a time for a loan. For example, if there are 3 borrowers for a loan, they want the first copy of the form to print the first 2 borrowers and then another copy of the form to print the 3rd. No matter how many copies are printed, they want the borrower information to be labeled as 'Borrower1' xyz and 'Borrower2' xyz. Also, there will be a LOT more fields returned on the real form, so the sample information below is very simplified test data.

Sample Data:

CREATE TABLE #t (LoanID VARCHAR(5), BorrowerName VARCHAR(20), BorrowerOrder INT);

GO

INSERT INTO #t VALUES

('::E', 'John Smith', 0)

, ('::E', 'Jane Smith', 1)

, ('::E', 'Rob Jackson', 2)

, ('AF_CF', 'Sloan Burton', 1)

, ('AF_CF', 'Virginia Burton', 2)

, ('AF_CF', 'John Hardeman', 0)

, ('::E', 'Mary Lamb', 4)

, ('::E', 'Patrick Stewart', 3)

So for loan 'AF_CF', the form should print twice:

1st copy: Borrower1 = Sloan Burton, Borrower2 = Virginia Burton

2nd copy: Borrower1 = John Hardeman, Borrower2 = NULL

We are on 2012, so I tried to do this with LEAD():

SELECT

Borrower1 = BorrowerName

, Borrower2 = LEAD(BorrowerName) OVER(ORDER BY BorrowerOrder)

FROM

#t AS t

WHERE

FileID = 'AF_CF'

But this returns a record for every borrower, so the form is printing 3 times:

1st: Borrower1 = Sloan Burton, Borrower2 = Virginia Burton

2nd: Borrower1 = Virginia Burton, Borrower2 = John Hardeman

3rd: Borrower1 = John Hardeman, Borrower2 = NULL

I don't want that 2nd record to return. This result is what makes me think of gaps and islands, but I don't know if the 2nd record is really an island since it's (1) not stored this way...it's returning this way because of the query and (2) it's not sequential data....

Finally, I tried restricting this by putting this into a CTE and then returning only the odd numbered records like I have below. This runs pretty quickly when dealing with one loan. But...I am concerned that the CTE will be slow when we run batches of loans.

Attempt with CTE:

--With CTE

;WITH cte AS

(SELECT

Borrower1 = BorrowerName

, Borrower2 = LEAD(BorrowerName) OVER(ORDER BY BorrowerOrder)

, RowNumber = ROW_NUMBER() OVER(ORDER BY BorrowerOrder)

FROM

#t AS t

WHERE

FileID = '::E'

)

SELECT

*

FROM

cte

WHERE

RowNumber % 2 = 1

Is there a better, cleaner way to do this? Or is the CTE the best way to go?