locked
Hashtable RRS feed

  • Question

  • Hi Everybody,

    I'd like to know some t-sql set of instructions for starting from this table ( key, value )

    Key Value
    1   NULL
    2   NULL
    3   NULL
    4   A
    5   NULL
    6   NULL
    7   NULL
    8   B
    9   NULL
    10  NULL
    11  NULL
    12  NULL
    13  NULL
    14  C

    to this:

    Key Value
    1   A
    2   A
    3   A
    4   A
    5   B
    6   B
    7   B 
    8   B
    9   C
    10  C
    11  C
    12  C
    13  C
    14  C

    Can anybody help me with some example to start from? is this a problem of Operative Research?

    Thanks.



    • Edited by gtgrade105 Tuesday, November 14, 2017 2:23 PM
    Tuesday, November 14, 2017 2:21 PM

All replies

  • this?

    SELECT t.Key,COALESCE(t.Value,t1.Value) AS Value
    FROM Table t
    CROSS APPLY (
    SELECT TOP 1 Value
    FROM Table
    WHERE Key > t.Key
    AND Value IS NOT NULL
    ORDER BY key
    )t1
    



    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by Xi Jin Wednesday, November 15, 2017 1:57 AM
    Tuesday, November 14, 2017 2:32 PM
  • yes and thank you but I don't understand this cryptic instruction.:)

    Tuesday, November 14, 2017 3:16 PM
  • yes and thank you but I don't understand this cryptic instruction.:)

    APPLY operator executes the subquery for each row in the table. The subquery checks for the first value which is not null and having key > current key value. COALESCE checks if current value is having value if yes it takes it otherwise it gives value returned by subquery

    Also see

    https://visakhm.blogspot.ae/2010/01/multipurpose-apply-operator.html


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, November 14, 2017 3:19 PM
  • Great!

    Tuesday, November 14, 2017 3:28 PM
  • Hi,

    Or you can try:

    create table #test 
    (
    [Key] int,
    [value] varchar(10)
    )
    
    insert into #test values
    (1,null),
    (2,null),
    (3,null),
    (4,'A'),
    (5,null),
    (6,null),
    (7,null),
    (8,'B'),
    (9,null),
    (10,null),
    (11,null),
    (12,null),
    (13,null),
    (14,'C')
    
    ;with cte as(
    select t1.[Key],t2.[value],ROW_NUMBER()Over(Partition By t1.[Key] Order By t2.[Value]) as rn
    from #test t1 
    join (
    	select * 
    	from #test
    	where value is not null ) t2
    on t1.[Key]<=t2.[Key] )
    
    select [Key],[Value]
    from cte
    where rn=1

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 15, 2017 2:04 AM
  • Great!

    Please mark the relevant posts which provided you the solution as answer for others benefit

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, November 15, 2017 5:04 AM
  • why not simpy this:

    select t.*,tt.value from #test t inner join 
     #test  tt 
     on t.chiave<=tt.chiave
     where tt.value is not null

    Wednesday, November 15, 2017 4:38 PM
  • Hi,

    Did you run the query? It will generate duplicate records.

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 16, 2017 1:42 AM
  •   SELECT b."Key" , on_nxt_KeyVal."Value" AS  KeyValue_OnNext
      FROM 
      (
    	 SELECT   curr."Key" , MIN( on_nxt."Key"   )  AS on_nxt_Key
    	 FROM yourTbl AS curr 
    	 LEFT JOIN yourTbl  AS  on_nxt ON   on_nxt."Value" IS NOT NULL AND  curr."Key"  <= on_nxt."Key"  
    	 GROUP BY  curr."Key" 
     ) b
      LEFT JOIN  yourTbl AS on_nxt_KeyVal    ON  b. on_nxt_Key = on_nxt_KeyVal."Key"



    • Edited by msbi_Dev Thursday, November 16, 2017 2:20 AM
    Thursday, November 16, 2017 2:11 AM