Home Forums SQL Server 2012 SQL Server 2012 - T-SQL Need sql help RE: Need sql help

  • December 5, 2018 at 3:43 pm

    #2015617

    saptek9 - Wednesday, December 5, 2018 9:55 AM

    i have two columns ID, Val with 

    IDVal
    1001X
    1002YY
    1003NNN
    1003Null
    1004 
    1005NULL
    1005ZZ
    1006NUll

    I need to have only non- null values if any ID has both null and non-null values + IDS which are with just null values.
    So expected data like below:

    IDVal
    1001X
    1002YY
    1003NNN
    1004 
    1005ZZ
    1006NUll

    Need to remove 1003,Null  1005, NUll rows as those ids already have non-null values

    Please advise the query.

    Hi 

    I am not sure whether this would solve your problem but here is my solution.Let me know if you find it useful.

    SELECT
    Q.*
    FROM
    (
    SELECT
      S.ID
    , S.Val
    , ROW_NUMBER() OVER(PARTITION BY ID ORDER BY CASE WHEN Val IS NOT NULL THEN 0 ELSE 1 END) AS #s
    FROM
    #Sample S
    )Q
    WHERE
     Q.#s=1