How to identify a person or id if it contains more than one row for a different column in SQL

Question

I have a table in which a person contains same values multiple times in another column.

For example:

  person     product    portal  count    indicator
  -----------------------------------------------
      1        10        5         2        y
      1        10        6         2        y
      1        15        7         1        y


select person, count(person) over(partition by product) 
from table A 

If a person contains same product multiple times then in this scenario I want to identify such population and want to give some type of indicator to it.

In the above example product 10 is appearing multiple times so I want to give an indicator to all rows of person.

Answer 1

WITH  C AS  
(SELECT    person, product, count(1) as Duplicate_product   
FROM  [table] A
GROUP  BY  person, product),
B AS 
(SELECT DISTINCT  person   
FROM  [table] A
GROUP BY person, product
HAVING COUNT(1) > 1)

SELECT  t.person, t.product, t.portal, 
CASE WHEN  B.person is null THEN 'N' ELSE 'Y' END  AS  indicator, ISNULL(C.Duplicate_product, 1) AS  [count] 

FROM  [table]  AS  t
INNER JOIN C ON t.person = C.person AND t.product = C.product
LEFT  JOIN B ON t.person = B.person;