Coalesce list of links between names into a single column

Question

I currently have two tables used to store relations between entities that look like this:

Entity_Table: Contains names and types (master or secondary) of entities

ENTITY_ID  |  ENTITY_NAME  |  ENTITY_TYPE
-----------------------------------------
1          |  'entity_1'   |  'master'
2          |  'entity_2'   |  'secondary'
3          |  'entity_3'   |  'secondary'
...

Links table: Contains relationship information between entities

MASTER_ID  |  SECONDARY_ID
--------------------------
1          |  2
1          |  3
...

Additional information:

• There are around ~5000 entities, each can have several links, some have no links
• These tables are used by an external c++ code

What I am looking for: A query that would return all entities and their linked entities in this format:

ENTITY_ID  |  ENTITY_TYPE  |  ENTITY_LINKS
------------------------------------------
1          |  'master'     |  2,3
2          |  'secondary'  |  1
3          |  'secondary'  |  1
...

I have tried using COALESCE and JOIN with little success, any ideas on how I could achieve this?

The links column has to be in a "csv-like" format so it can be used directly by the c++ code behind without massive for loops or having one query per entity which slows things down considerably.

Views are nice too if you know how to put this into a view?

Answer 1

I think this will give you what you are after. This uses STRING_AGG which requires SQL Server 2017 or newer. If you're using an older version of SQL Server you can use STUFF and FOR XML PATH (which is what I think Harry was talking about above).

select e.entity_id, e.entity_type, string_agg(l.secondary_id, ',') as entity_links
from entity_table e
inner join links l on l.master_id = e.entity_id
group by e.entity_id, e.entity_type
union
select e.entity_id, e.entity_type, string_agg(l.master_id, ',') as entity_links
from entity_table e
inner join links l on l.secondary_id = e.entity_id
group by e.entity_id, e.entity_type

Think this would give you what you need using STUFF.

select e.entity_id, e.entity_type, STUFF((SELECT distinct ',' + cast(l.secondary_id as varchar(20))
         from links l 
         where l.master_id = e.entity_id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'') as entity_links
from entity_table e
inner join links l on l.master_id = e.entity_id
group by e.entity_id, e.entity_type
union
select e.entity_id, e.entity_type, STUFF((SELECT distinct ',' + cast(l.master_id as varchar(20))
         from links l 
         where l.secondary_id = e.entity_id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'') as entity_links
from entity_table e
inner join links l on l.secondary_id = e.entity_id
group by e.entity_id, e.entity_type