Sql-server – Get ids only where one id is null and other isn’t

sql server

I have a table as such where id's are repeating .

    +-------+------+
      ID      NUM
    +-------+------+
      1        50
      1        51
      2        52
      2        NULL
      3        NULL
      3        NULL 
      4        53
      4        54
      4        NULL
      5        NULL
      6        55
      7        NULL
    +-------+------+

I want to get table where i only have the ids which have both null and non null values in the column labelled (num)

like so

 +------+-----+
   ID     NUM
 +------+-----+
   2      52
   2      NULL
   4      53
   4      54
   4      NULL
 +------+-----+

Best Answer

WITH cte AS ( SELECT id, 
                     num, 
                     COUNT(*) OVER (PARTITION BY id) total,
                     COUNT(num) OVER (PARTITION BY id) notnull
              FROM mytable )
SELECT id, num
FROM cte
WHERE notnull * (total - notnull) > 0

OR

WITH cte AS ( SELECT id, 
                     num, 
                     FIRST_VALUE(num) OVER (PARTITION BY id ORDER BY num) mini,
                     FIRST_VALUE(num) OVER (PARTITION BY id ORDER BY num DESC) maxi
              FROM mytable )
SELECT id, num
FROM cte
WHERE mini IS NULL
  AND maxi IS NOT NULL