How to put single flag if same cust have different account

Question

Hi coder,

 

Let me explain what is my requirment. I need to check if i have customers with different productid if yes then i have to check if they have any producttype null or not in case any of the customerid have NULL producttype then flag for both the customerid should be N else Y 

 For example :I have a table in which i have many columns. PFB the table stucture

 Customerid   productid  producttype

  1                    a              x

  1                    b            Null

  2                    c             y

  2                    d             y

  3                    e             z

  3                    f              Null

 

what i want is like below:

 

Customerid  Productid   Productype  flag

1                     a                x                N

1                     b               Null             N

2                     c                y                Y                       

2                     d                y                Y

3                     e                z                N

3                     f               Null             N

 

till now what i have done

 

;with cte as

 

(

select * from test where customerid  in

(select  customerid  from test  group by customerid having count(*) >1

) )

from this i collect all the customerid who have more then one productid and different producttpe now i want to add the flag part.

 

Please let me know if this approach is good and how can i achieve the next thing.

 

Thanks in advance !!