Heh... my first recommendation is to stop cutting yourself short by posting test-data-creation code that works only in 2008 and above. There are a whole lot of some really helpful and smart folks that could help you that are still stuck in 2005 depending on what environment they might currently be sitting in. Other than that, you did an outstanding job of posting readily consumable data we can understand. Well done.

With that thought in mind, I've converted your test data creation script to work in 2005 and up. I've also made the correction you identified and I made the BorrowerOrder non-sequential as you suggested it may be in real life.

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

GO

INSERT INTO #t

SELECT '::E' , 'John Smith' , 0 UNION ALL

SELECT '::E' , 'Jane Smith' , 1 UNION ALL

SELECT '::E' , 'Rob Jackson' , 2 UNION ALL

SELECT 'AF_CF' , 'Sloan Burton' , 0 UNION ALL

SELECT 'AF_CF' , 'Virginia Burton' , 2 UNION ALL

SELECT 'AF_CF' , 'John Hardeman' , 4 UNION ALL

SELECT '::E' , 'Mary Lamb' , 4 UNION ALL

SELECT '::E' , 'Patrick Stewart' , 3

;

I may have read a little too much into your request but my understanding is that you actually need to control the letters and the copies of the letters. No better way to do that than to create a row-per-copy relationship for a result set so that all you have to do is "field replacement" from a table. In other words, denormalize the hell out of the data in the form of a result set. 😀 Here's the code to do that. This uses an ancient method known as a "CrossTab" and it can be much faster than a Pivot. Please see the following article for more on that subject (which is followed by the code for this problem)...

[font="Arial Black"]http://www.sqlservercentral.com/articles/T-SQL/63681/[/font][/url]

A little math along with the CrossTab code makes short work of it all.

WITH ctePreSort AS

( --=== Create unbroken sequences starting at zero for each LoanID in BorrowerOrder order

SELECT LoanID

,BorrowerName

,SortOrder = ROW_NUMBER() OVER (PARTITION BY LoanID ORDER BY BorrowerOrder)-1

FROM #t

) --=== Do a string aggregation (CrossTab) to pivot the data correctly for use as a print control table.

SELECT LoanID

,CopyNumber = SortOrder/2+1

,Borrower1 = MAX(CASE WHEN SortOrder%2 = 0 THEN BorrowerName ELSE '' END)

,Borrower2 = MAX(CASE WHEN SortOrder%2 = 1 THEN BorrowerName ELSE '' END)

FROM ctePreSort

GROUP BY LoanID,SortOrder/2

ORDER BY LoanID,SortOrder/2

;

Here are the results...

LoanID CopyNumber Borrower1 Borrower2

------ ---------- ------------- ---------------

::E 1 John Smith Jane Smith

::E 2 Rob Jackson Patrick Stewart

::E 3 Mary Lamb

AF_CF 1 Sloan Burton Virginia Burton

AF_CF 2 John Hardeman

(5 row(s) affected)

I think you were right to be concerned. I haven't used it myself, yet, but I've seen some performance tests by people I trust and LEAD/LAG doesn't measure up to what I had expected (doesn't come close, actually). I can tell you that, especially with an index on the GROUP BY columns, the CrossTab type of string aggregation I did above absolutely flies (although I admit that I have NOT tested this particular bit of code but have severely tested nearly identical code).