locked
TSQL / Graph Database Query RRS feed

  • Question

  • Thanks for reading. I have a requirement to group people together in a unusual fashion. I don't have any control over source data so I can only manipulate it as received. I've solved this with a recursive CTE but is a very expensive query which I'd hoped that a I could get some advise. I've even begin to try with graph database.

    I get data in this fashion as a relationship format

    Entity 1 | Entity 2 |  ( Family desired output )

    A  |  B  |  1  ( <- this column being what I'd like to generate )

    B  |  A  |  1

    C  |  A  |  1

    Z  |  Y   |  2

    A and B match, C matches to A so they are all become 'Family 1.'  Z and Y match so they become 'Family 2.' The very ugly way I solved this was by creating a cursor and going row by row and passing the Entity 1 ID to a function which used a recursive CTE to locate any matches. I'm hoping for a better solution.

    Thank you.

    Monday, April 20, 2020 8:37 PM

All replies

  • Hi Brian52,

    You could kindly share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …), so that we’ll get a right direction and make some test.

    --Do you only have 4 conditions?
    create table #t(Entity1 varchar(10),Entity2 varchar(10))
    insert #t values('A','B'),('B','A'),('C','A'),('Z','Y')
    
    select Entity1 ,Entity2 ,
           case when Entity1 ='A' and Entity2 ='B' then 1
    	        when Entity1 ='B' and Entity2 ='A' then 1
    		when Entity1 ='C' and Entity2 ='A' then 1
    		when Entity1 ='Z' and Entity2 ='Y' then 2
    	   end as Family
    from #t 

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, April 21, 2020 2:14 AM
  • Hi Lily,

    Thanks for the response. Sorry I should have mentioned that table would contain millions of records. I'm looking for a way to dynamically generate family id's since the family logic would impossible to code for up front.

    Thanks

    Tuesday, April 21, 2020 11:27 AM
  • Hi Brian52,

    Thank you very much for your reply. Graph database is a more suitable option to store certain complex relationships, pulling metadata into graph tables could be a convenient way to understand relationships from table to table with a schema.

    refer to: SQL Graph Database – the new feature in SQL Server 2017.

    Querying database schema using graph tables.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, April 22, 2020 7:03 AM