Need to bring in all combinations from the data

  • 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

  • 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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • 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 3 months, 3 weeks ago by  komal145.

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

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