Need to bring in all combinations from the data

Question

Need to bring in all combinations from the data

komal145

SSCrazy Eights

Points: 9915
More actions
January 24, 2022 at 8:24 pm

#3976687
```
CREATE TABLE RELATIONSHIP
(
CASE  int,
INDIVIDUALID INT,
RELATIONSHIPID INT,
)


INSERT INTO RELATIONSHIP (CASE,INDIVIDUALID,RELATIONSHIPID)  VALUES (1001,1234,4567)

INSERT INTO RELATIONSHIP (CASE,INDIVIDUALID,RELATIONSHIPID)  VALUES (1001,1234,4568)
```
hi ,
I am trying to build a relationship table. Where i have individual id's and i need to get their relations with another individual , who have same CASENUMBER.
example : I have this data now :

Needed this ( Ignore the relationship, i am just giving example to understand data)

here is the DDL attaached:

Thanks In advance

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 100947 More actions · Answer 1

Just off the top of my head, here's on way:

DROP TABLE IF EXISTS #RELATIONSHIP_FLATTENED;
SELECT TOP (0) [CASE], INDIVIDUALID AS ID
INTO #RELATIONSHIP_FLATTENED
FROM RELATIONSHIP
--
CREATE UNIQUE CLUSTERED INDEX [RELATIONSHIP_FLATTENED__CL] 
    ON #RELATIONSHIP_FLATTENED ( [CASE], ID ) 
    WITH ( FILLFACTOR = 100 );
--
INSERT INTO #RELATIONSHIP_FLATTENED
SELECT DISTINCT ca1.[CASE], ca1.ID
FROM RELATIONSHIP
CROSS APPLY (
    SELECT [CASE], INDIVIDUALID AS ID
    UNION ALL
    SELECT [CASE], RELATIONSHIPID
) AS ca1
ORDER BY [CASE], ID

--main query
SELECT RF1.[CASE], RF1.ID AS INDIVIDUALID, RF2.ID AS RELATIONSHIPID
FROM #RELATIONSHIP_FLATTENED RF1
INNER JOIN #RELATIONSHIP_FLATTENED RF2 ON RF1.[CASE] = RF2.[CASE] AND RF1.ID <> RF2.ID

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

komal145 SSCrazy Eights Points: 9915 More actions · Answer 2

Thank you. I was able to update my logic based on the query you provided. Is there a way in your query to get the relationship for those i gave example. I need that field too , so , i can derive relationship based on actual relation and Gender.

This reply was modified 2 years, 3 months ago by komal145.