Take the 2-minute tour ×
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:

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<=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<=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<=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<=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<=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.

DECLARE @Old TABLE ( 
  id         INT, 
  rank       INT, 
  linenumber INT, 
  sometext   VARCHAR(1000)) 
DECLARE @New TABLE ( 
  id           INT, 
  rank         INT, 
  combinedtext VARCHAR(1000)) 


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

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

SELECT * 
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: projectdmx.com/tsql/rowconcatenate.aspx 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:

CREATE FUNCTION fnCombinedText
(
    @ID int,
    @Rank int
)
RETURNS varchar(MAX)
AS
BEGIN

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

SELECT @CombinedText = @CombinedText + SomeText
FROM oldTable
WHERE [ID] = @ID 
AND [Rank] = @Rank
ORDER BY [Rank]

RETURN @CombinedText

END

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:

CREATE TABLE #RankMap
(
    newID int,
    newRank int,
    oldID int,
    oldRank int
)
INSERT INTO #RankMap
SELECT newID, newRank, oldID, oldRank
FROM
(
    SELECT
        n.id AS newID,
        n.rank AS newRank,
        o.id AS oldID,
        o.rank as oldRank,
        RANK() OVER(PARTITION BY n.rank ORDER BY o.rank DESC) AS topRank
    FROM
        newtable AS n 
        LEFT OUTER JOIN (SELECT DISTINCT id, rank FROM oldtable) AS o
            ON n.id = o.id
            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:

SELECT
    newID,
    newRank,
    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

 
discard

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.