Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

  1. 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.
  2. 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:



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:

1,1,1,the qu  
1,1,2,ick br  
1,2,1,some te  
2,7,1,jumped ov  
2,7,2,er the  
2,13,2,le text  


1,2,some text
2,13,sample text
2,14,sample text

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 and oldtable.rank=(select top 1 rank from oldtable where and oldtable.rank<=newtable.rank order by rank desc) and oldtable.linenumber=1),'') +
coalesce ((select top 1 sometext from OldTable where and oldtable.rank=(select top 1 rank from oldtable where and oldtable.rank<=newtable.rank order by rank desc) and oldtable.linenumber=2),'') +
coalesce ((select top 1 sometext from OldTable where and oldtable.rank=(select top 1 rank from oldtable where and oldtable.rank<=newtable.rank order by rank desc) and oldtable.linenumber=3),'') +
coalesce ((select top 1 sometext from OldTable where and oldtable.rank=(select top 1 rank from oldtable where and oldtable.rank<=newtable.rank order by rank desc) and oldtable.linenumber=4),'') +
coalesce ((select top 1 sometext from OldTable where and oldtable.rank=(select top 1 rank from oldtable where and oldtable.rank<=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...

share|improve this question

3 Answers 3

up vote 1 down vote accepted

This should work, I will clean it up later so its more efficient.

  id         INT, 
  rank       INT, 
  linenumber INT, 
  sometext   VARCHAR(1000)) 
  id           INT, 
  rank         INT, 
  combinedtext VARCHAR(1000)) 

;WITH combinedresults(ctid, id, rank, linenumber, combinedtext) 
     AS (SELECT 0, 
                CAST (sometext AS VARCHAR(8000)) 
         FROM   @Old o 
                            FROM   @Old 
                            WHERE  id = 
                                   AND rank = o.rank 
                                   AND linenumber < o.linenumber) 
         UNION ALL 
         SELECT ctid + 1, 
                ct.combinedtext + o.sometext 
         FROM   @Old o 
                INNER JOIN combinedresults ct 
                  ON = 
                     AND ct.rank = o.rank 
         WHERE  o.linenumber > ct.linenumber) 

SET    combinedtext = ct.combinedtext 
FROM   @New n 
                          MAX(o.rank) orank 
                   FROM   @new n 
                          INNER JOIN @Old o 
                            ON = 
                               AND o.rank <= n.rank 
                   GROUP  BY, 
                             n.rank) r 
         ON = 
            AND n.rank = r.rank 
       INNER JOIN (SELECT id, 
                          MAX(ctid) ctid 
                   FROM   combinedresults ct 
                   GROUP  BY, 
                             ct.rank) r2 
         ON = 
            AND r2.rank = r.orank 
       INNER JOIN combinedresults ct 
         ON = 
            AND ct.rank = r.orank 
            AND ct.ctid = r2.ctid 

FROM   @New 
share|improve this answer
Didn't seem to work for me...After taking 2minutes 30 seconds (longer than the current while-loop method unfortunately), most of the CombinedText columns didn't get updated, and the ones that did were inconsistent (the same ID+Rank could have different values). When ran against the example case, the 3rd row (ID 2, Rank 14) has a blank CombinedText. – Adam Apr 18 '11 at 16:39
I created temp tables of the example data and seemed to work for me. Though for the example case the input's do not have a id 2 rank 14. This could probably be sped up by only combining text for records that are in the other table. I did some refactoring, so maybe I messed something up while cleaning it up. Can you export larger sample data to test against, csv? – Thx Apr 18 '11 at 18:25
"input's do not have a id 2 rank 14"- See consideration 1 on the question. This is often the case, the query should pick the rank 13 value if 14 does not exist. I'm going to wait on optimizations until I can verify the query works. I'll update the post with more elaborate example data. – Adam Apr 18 '11 at 19:01
Sorry, missed that requirement the first time. Also fixed the issue of multiple concatenations. This should work but it could be cleaned up a little bit. – Thx Apr 18 '11 at 21:41
Legendary! That seems to work work well, takes 17 seconds after the obvious optimizations, which is a nice improvement. I'd upvote if I have the rep (takes 15?), but suffice to say Thank You Very Much! – Adam Apr 19 '11 at 23:31

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.

share|improve this answer
Are you sure it requires row by row? I came across this page: where there are solutions for a simpler cases (where the primary key is not composite). Why is it required in this case? – Adam Apr 15 '11 at 16:57
With newer versions of SQL Server CTEs can be used to make this a lot easier. Without knowing the version number row by row processing in a cursor would be needed. A CTE will be doing something very similar under the covers. – mrdenny Apr 21 '11 at 21:10
Yea, looks like that's what Thx did in his/her version (as noted, using MSSQL2005). For whatever reason they seemed to work quite a bit faster than manual cursor-ing. Someday I'll probably go back and see if it's actually making less sub queries or what. – Adam Apr 21 '11 at 23:49

I'm not good with CTE yet, so here's my take on the question using a more traditional approach without cursors.

Requirement #2 reminds me of a project I worked on that required producing a comma-separated concatenation of the values on a column grouped by some category. The solution I used required a UDF to produce the concatenated string using the provided category id.

Below is the adapted UDF using ID and Rank parameters:

    @ID int,
    @Rank int
RETURNS varchar(MAX)

DECLARE @CombinedText varchar(MAX)
SET @CombinedText = ''

SELECT @CombinedText = @CombinedText + SomeText
FROM oldTable
AND [Rank] = @Rank

RETURN @CombinedText


Requirement #1 can be accomplished by joing the newTable Rank's with all distinct equal or less oldTable Rank's and getting the best/top match:

    newID int,
    newRank int,
    oldID int,
    oldRank int
SELECT newID, newRank, oldID, oldRank
    SELECT AS newID,
        n.rank AS newRank, AS oldID,
        o.rank as oldRank,
        RANK() OVER(PARTITION BY n.rank ORDER BY o.rank DESC) AS topRank
        newtable AS n 
        LEFT OUTER JOIN (SELECT DISTINCT id, rank FROM oldtable) AS o
            ON =
            AND n.rank >= o.rank
) AS matchEqualLess
WHERE topRank = 1

Now that we have the mapped oldTable Rank's, we can use the UDF to generate the CombinedText's:

    dbo.fnCombinedText(oldID, oldRank) AS CombinedText
FROM #RankMap

--Below is the resultset:
newID       newRank     CombinedText
----------- ----------- --------------------
1           2           some text
3           4           ABCDEFGHIJKL
3           5           ABCDEFGHIJKL
2           13          sample text
2           14          sample text
3           50          XYZ
3           55          XYZ

The main downside for this solution is that each call to the UDF fnCombinedText() is essentially a separate SELECT on the oldTable. I bet this approach can be ported to a more scalable CTE query. And I guess I should really get around to mastering CTE, too.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.