Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2009
    Posts
    9

    Red face Unanswered: How do i make this query ?

    scope of my work is
    my select statement is having null value in a column as shown below,
    1 first
    2 NULL
    3 2nd
    4 NULL
    5 NULL
    6 NULL

    what i want if there is any null value found previous column will be placed to current row... the result of my query should be like this

    1 first
    2 first
    3 2nd
    4 2nd
    5 2nd
    6 2nd


    what i was thinking to use a udf function where I can place value in Context_Info, if there is value found in row calling the function and set this value in Context_Info otherwise null value found set the column data from Context_Info.

    Unfortunately, udf can't change the state out of the world...
    help out plz..

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This is not trivial. I suggest you create DDL and DML so anyone interested can quickly create your environment.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    DECLARE @t table (
       id  int
     , val char(5)
    )
    
    INSERT INTO @t (id, val)
              SELECT 1, 'first'
    UNION ALL SELECT 2, NULL
    UNION ALL SELECT 3, '2nd'
    UNION ALL SELECT 4, NULL
    UNION ALL SELECT 5, NULL
    UNION ALL SELECT 6, NULL
    
    
    /* Desired output
     id | val
    ----+-------
     1  | first
     2  | first
     3  | 2nd
     4  | 2nd
     5  | 2nd
     6  | 2nd
    */
    
    --Add an extra row which breaks the sequence
    INSERT INTO @t (id, val) VALUES (9, NULL)
    
    ; WITH windowed_set As (
      SELECT id
           , val
           , Row_Number() OVER (ORDER BY id ASC) As row_num
      FROM   @t
    )
    , cte AS (
      SELECT id
           , val
           , row_num
      FROM   windowed_set
      WHERE  row_num = 1
    
      UNION ALL
        SELECT b.id
             , Coalesce(b.val, a.val)
             , b.row_num
        FROM   cte As a
         INNER
          JOIN windowed_set As b
            ON b.row_num = a.row_num + 1
    )
    SELECT id
         , val
    FROM   cte
    George
    Home | Blog

  4. #4
    Join Date
    Jun 2009
    Posts
    9
    George...! excellent job done...
    Its working thank alot.

  5. #5
    Join Date
    Jun 2009
    Posts
    26
    I may be misunderstanding what your trying but isn't this easily solved using the isnull function?

    Eg.

    select isnull(column1,column2)
    from table

    In this case for rows where column1 is null column2 is inserted into the output?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I think you are misunderstanding
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    George,

    Nice !
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Actually, it's a horrible, ugly, recursive solution, but it works!

    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •