Query to normalize table/combine row text I have a table (call it oldTable) with columns like so:

ID (int),Rank (int),TextLineNumber (int),SomeText (varchar)

The primarykey is multi-part: ID+Rank+TextLineNumber.

I'm trying to transform/join it into another table (call it newTable) with columns like so:

ID (int), Rank (int), CombinedText (varchar)

and the primary key would be ID+Rank.

ID and Rank on the new table are already populated, but I need a query that would update the CombinedText column of the newTable with the following considerations:


The Rank given on the new table may not exist on the old table, in which case it needs to pick the highest available rank from the old table that is not greater than the rank on the new table.
The CombinedText column is a string concatenation of the "SomeText" column from the old table, concatenated in order of "TextLineNumber" using the Rank found from the first consideration.


Here's some example data:

old-
http://i54.tinypic.com/jq0vmx.png

new-
http://i53.tinypic.com/dhfyn8.png

I'm using MSSql 2005 if that matters.
I currently do this using T-SQL and while loops, but it's become a serious performance bottle neck (taking about 1 minute for 10000 rows).

Edit: Expanded example data in CSV:
Old:  

ID,Rank,LineNumber,SomeText
1,1,1,the qu  
1,1,2,ick br  
1,1,3,own  
1,2,1,some te  
1,2,2,xt  
1,3,1,sample  
2,7,1,jumped ov  
2,7,2,er the  
2,7,3,lazy  
2,13,1,samp  
2,13,2,le text  
3,1,1,ABC  
3,1,2,DEF  
3,1,3,GHI  
3,1,4,JKL  
3,50,1,XYZ


New:  

ID,Rank,CombinedText
1,2,some text
2,13,sample text
2,14,sample text
3,4,ABCDEFGHIJKL
3,5,ABCDEFGHIJKL
3,50,XYZ
3,55,XYZ


edit2:
Here's an example query that I found that does work but isn't fast enough (relying on multiple sub-queries):

update newtable set combinedtext = 
coalesce ((select top 1 sometext from OldTable where OldTable.id=newtable.id and oldtable.rank=(select top 1 rank from oldtable where oldtable.id=newtable.id and oldtable.rank&lt;=newtable.rank order by rank desc) and oldtable.linenumber=1),'') +
coalesce ((select top 1 sometext from OldTable where OldTable.id=newtable.id and oldtable.rank=(select top 1 rank from oldtable where oldtable.id=newtable.id and oldtable.rank&lt;=newtable.rank order by rank desc) and oldtable.linenumber=2),'') +
coalesce ((select top 1 sometext from OldTable where OldTable.id=newtable.id and oldtable.rank=(select top 1 rank from oldtable where oldtable.id=newtable.id and oldtable.rank&lt;=newtable.rank order by rank desc) and oldtable.linenumber=3),'') +
coalesce ((select top 1 sometext from OldTable where OldTable.id=newtable.id and oldtable.rank=(select top 1 rank from oldtable where oldtable.id=newtable.id and oldtable.rank&lt;=newtable.rank order by rank desc) and oldtable.linenumber=4),'') +
coalesce ((select top 1 sometext from OldTable where OldTable.id=newtable.id and oldtable.rank=(select top 1 rank from oldtable where oldtable.id=newtable.id and oldtable.rank&lt;=newtable.rank order by rank desc) and oldtable.linenumber=5),'')


It also assumes a max line number of 5 which may not be the case. I don't mind hard-coding the linenumbers in all the way to a max of 20 if that's what it takes, but ideally it would be able to account for them differently. Getting execution time under 20 seconds (the actual data) is the goal...
You can create a function that stings the values together using a cursor within the function, but that's about your only option.  You'll need to do row by row processing to make this happen.