Q:

How to put single flag if same cust have different product

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 !!  

A:

You are on right track.  I haved used CTE but with lesser complicated inner query. 
Try this :
Create table #temp(Customerid int,   productid varchar(1), producttype varchar(1))
insert into #temp values
  (1,'a','x'),   
  (1,'b',Null),
  (2,'c','y'),   
  (2,'d','y'),   
  (3,'e','z'),   
  (3,'f',Null)

;with cte as
(
select distinct customerid, 'T' as tempflag from #temp where  producttype is null 
)
Select b.*,
case  a.tempflag when 'T' then 'N' else 'Y' end as Flag
 from cte a
right join #temp b
on a.customerid = b.customerid

output : 
Customerid  productid producttype 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

