I have a table (o_relationships) which records relationships between siblings. I need some help to develop some sql which links together the siblings into family groups.
Please see the attached:
-fig1. current output from o_relationships table. SUBJECT_ID and SUBJECT_SURNAME are the subjects details - SIBLING_ID and SIBLING_SURNAME are the details of the subjects sibling/ relationship.
-fig2. Desired output - the output needs to list each subject once and two new columns (the FAMILY_NAME and FAMILY_ID). The family name is a combination of the SUBJECT_SURNAME and the SIBLING_SURNAME (s).
Some examples of how the family name is identified -
-subject_id 319058- the family_name is KHAN/ MCMILLAN (the subjects surname is MCMILLAN and the subjects siblings surname is KHAN).
-subject_id 88307 - the family_name is BROOKFIELD (the subjects surname is BROOKFIELD and the subject does not have any siblings)
-subject_id 14484 - the family_name is GRIMMER (the subjects surname is GRIMMER, the subject has multiple siblings with the surname of GRIMMER)
The family_ID is the unique identifier for the family.
Any help to produce the sql to achieve this result is appreciated. I don’t have the ability to run procedures so this would need to be done in sql.