Fill ID with draggable data in SQL Server table

Question

I am trying to solve this problem; I have a table like this:

SELECT [ID], [STRING] FROM Test

ID      STRING
38      Hi 38
39      Hi 39
42      Hi 42
46      Hi 46
47      Hi 47
49      Hi 49

TDL:

CREATE TABLE #Test ([ID] int, [STRING] varchar (50));

INSERT INTO #Test
    ([ID], [STRING])
VALUES
    (38, 'Hi 38'),
    (39, 'Hi 39'),
    (42, 'Hi 42'),
    (46, 'Hi 46'),
    (47, 'Hi 47'),
    (49, 'Hi 49')
;

With IDhow INTand STRINGhow VARCHAR(50), and I have to write a new SELECT query to get this result:

ID      STRING
38      Hi 38
39      Hi 39
40      Hi 39
41      Hi 39
42      Hi 42
43      Hi 42
44      Hi 42
45      Hi 42
46      Hi 46
47      Hi 47
48      Hi 47
49      Hi 49

Fill in progressive identifiers and use the previous value STRINGfor new identifiers.

I tried with CTE, but with no results.

Answer 1

, , xml .

DECLARE @Table TABLE
    (
      ID INT ,
      STRING VARCHAR(50)
    )
INSERT  INTO @Table
VALUES  ( 38, 'Hi 38' )
,       ( 39, 'Hi 39' )
,       ( 42, 'Hi 42' )
,       ( 46, 'Hi 46' )
,       ( 47, 'Hi 47' )
,       ( 49, 'Hi 49' );
WITH    cte
          AS ( SELECT   ID ,
                        STRING ,
                        CAST(REPLICATE('<k>1</k>',
                                       LEAD(ID) OVER ( ORDER BY ID ) - ID) AS XML) AS x
               FROM     @Table
             ),
        cte2
          AS ( SELECT   a.ID ,
                        a.STRING ,
                        ROW_NUMBER() OVER ( PARTITION BY a.ID ORDER BY ID )
                        - 1 AS ID2
               FROM     cte AS a
                        OUTER APPLY x.nodes('/k') t ( x )
             )
    SELECT  ID + ID2 AS ID ,
            STRING
    FROM    cte2 AS a
    ORDER BY a.ID

Answer 2

SQL Server 2008

;WITH    id_rn ( id, string, rn )
          AS ( SELECT   id ,
                        string ,
                        ROW_NUMBER() OVER ( ORDER BY id )
               FROM     t
             ),
        id_min_max ( min_id, max_id )
          AS ( SELECT   MIN(id) ,
                        MAX(id)
               FROM     t
             ),
        rows ( n )
          AS ( SELECT   min_id
               FROM     id_min_max
               UNION ALL
               SELECT   n + 1
               FROM     rows
               WHERE    n < ( SELECT    max_id
                              FROM      id_min_max
                            )
             )
    SELECT  COALESCE(r.n, i1.id) AS id ,
            i1.string AS string
    FROM    id_rn AS i1
            LEFT JOIN id_rn AS i2 ON i2.rn = i1.rn + 1
            LEFT JOIN rows AS r ON r.n BETWEEN i1.id AND i2.id - 1
    ORDER BY id
OPTION  ( MAXRECURSION 0 );

---

;WITH    id_occ ( id, string, #id_missing_occ )
          AS ( SELECT   id ,
                        string ,
                        LEAD(id) OVER ( ORDER BY id ) - id - 1
               FROM     t
             ),
        max_id_occ ( #max_generated_rows )
          AS ( SELECT   MAX(#id_missing_occ) + 1
               FROM     id_occ
             ),
        rows ( n )
          AS ( SELECT   1
               UNION ALL
               SELECT   n + 1
               FROM     rows
               WHERE    n < ( SELECT    #max_generated_rows
                              FROM      max_id_occ
                            )
             )
    SELECT  id + COALESCE(n - 1, 0) ,
            string
    FROM    id_occ AS i
            LEFT JOIN rows AS r ON r.n <= i.#id_missing_occ + 1
                                   AND #id_missing_occ > 0
OPTION  ( MAXRECURSION 0 );

Answer 3

Tally, , ID, . Tally , :

WITH Tally (n) AS
(
    -- 1000 rows
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)
), Min_Max_CTE(min_id, max_id) AS
(
   SELECT MIN(ID) AS min_id, MAX(ID) AS max_id
   FROM mytable
)
SELECT ID, MAX(STRING) OVER (PARTITION BY grp) AS STRING
FROM (
   SELECT t1.n AS ID, t3.STRING,
          MAX(t3.ID) OVER (ORDER BY t1.n) AS grp
   FROM Tally As t1
   CROSS JOIN Min_Max_CTE AS t2
   LEFT JOIN mytable AS t3 ON t3.ID = t1.n
   WHERE t1.n BETWEEN t2.min_id AND t2.max_id) AS t

. SQL Server 2012 +.

Answer 4

:

    WITH compteur(id, minval, maxval) AS
    (
        SELECT (SELECT min(id) FROM test) , (SELECT min(id) FROM test) , (SELECT max(id)  FROM test) 
        UNION ALL
        SELECT id+1, minval, maxval FROM compteur 
        WHERE id < maxval
    )
    SELECT f1.id, f3.string FROM compteur f1
    OUTER APPLY
            (
            SELECT top 1 * FROM test f2
            WHERE f1.id>=f2.id
            ORDER BY f2.id DESC

            ) f3 
    OPTION (MAXRECURSION 1000);