4 added 1 characters in body
source | link
DECLARE @Old TABLE ( 
  id         INT, 
  rank       INT, 
  linenumber INT, 
  sometext   VARCHAR(1000)) 
DECLARE @New TABLE ( 
  id           INT, 
  rank         INT, 
  combinedtext VARCHAR(1000)) 


WITH;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 
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 
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 
3 Formatting
source | link

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

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 

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

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 
2 added 554 characters in body
source | link

This worked in my tests. Someone can probably improve uponshould work, I will clean it as wellup later so its more efficient.

;WITH combinedresults( id, rank, linenumber, combinedtext) 
     AS (SELECT id, 
                rank, 
                linenumber, 
                CAST (somettext AS VARCHAR(8000)) 
         FROM   @original o 
         WHERE  NOT EXISTS (SELECT TOP 1 1 
                            FROM   @original 
                            WHERE  id = o.id 
                                   AND rank = o.rank 
                                   AND linenumber < o.linenumber) 
         UNION ALL 
         SELECT o.id, 
                o.rank, 
                o.linenumber, 
                ct.combinedtext + o.somettext 
         FROM   @original 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 combinedresults ct 
         ON n.id = ct.id 
            AND n.rank = ct.rank 
       INNER JOIN (SELECT id, 
                          rank, 
                          MAX(linenumber) AS linenumber 
                   FROM   combinedresults 
                   GROUP  BY id, 
                             rank) r 
         ON ct.linenumber = r.linenumber 
            AND ct.id = r.id 
            AND ct.rank = r.rank 

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

This worked in my tests. Someone can probably improve upon it as well.

;WITH combinedresults( id, rank, linenumber, combinedtext) 
     AS (SELECT id, 
                rank, 
                linenumber, 
                CAST (somettext AS VARCHAR(8000)) 
         FROM   @original o 
         WHERE  NOT EXISTS (SELECT TOP 1 1 
                            FROM   @original 
                            WHERE  id = o.id 
                                   AND rank = o.rank 
                                   AND linenumber < o.linenumber) 
         UNION ALL 
         SELECT o.id, 
                o.rank, 
                o.linenumber, 
                ct.combinedtext + o.somettext 
         FROM   @original 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 combinedresults ct 
         ON n.id = ct.id 
            AND n.rank = ct.rank 
       INNER JOIN (SELECT id, 
                          rank, 
                          MAX(linenumber) AS linenumber 
                   FROM   combinedresults 
                   GROUP  BY id, 
                             rank) r 
         ON ct.linenumber = r.linenumber 
            AND ct.id = r.id 
            AND ct.rank = r.rank 

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

1
source | link