Group Items together that share the same ID Code, then indicate that the item is part of a group-CodePudding

Suppose I have items in a table that resemble the following:

Item	FamilyCode
ABR123	ABR
ABR456	ABR
BCL369	BCL
BCL987	BCL
AEL877	AEL

I would like to notate whether an item is part of a family based on the whether there are other items that exist with this same family code. I would like to add an output column such as "Is Part of a Family", Yes/No.

The output would be as follows:

Item	FamilyCode	Is Part of a Family?
ABR123	ABR	Yes
ABR456	ABR	Yes
BCL369	BCL	Yes
BCL987	BCL	Yes
AEL877	AEL	No

CodePudding user response：

You could use a semi-self join with exists

select Item, FamilyCode, 
  case when exists (
    select * from t t2
    where t2.FamilyCode = t.FamilyCode and t2.Item != t.items
  ) then 'Yes' else 'No' end [Is part of a family]
from t;

CodePudding user response：

I think this can be accomplished by counting the number of rows with the same FamilyCode and, if it's greater than 1, mark it as part of a family. Something like this:

with cte as (
    select * from (values
        ('ABR123', 'ABR'),
        ('ABR456', 'ABR'),
        ('BCL369', 'BCL'),
        ('BCL987', 'BCL'),
        ('AEL877', 'AEL')
    ) as x([Item], [FamilyCode])
)
select *,
    [IsPartOfFamily] =
        case when count(*) over (partition by [FamilyCode]) > 1 
        then 1
        else 0 end
from cte;