Category tree of categories / subcategory and display subcategories in one line

Question

I have a hierarchical table of regions and subregions, and I need to list the tree of regions and subregions (which is easy), but I also need a column that displays for each region all the ides of its subregions.

For instance:

id name superiorId ------------------------------- 1 RJ NULL 2 Tijuca 1 3 Leblon 1 4 Gavea 2 5 Humaita 2 6 Barra 4 

I need the result:

 id name superiorId sub-regions ----------------------------------------- 1 RJ NULL 2,3,4,5,6 2 Tijuca 1 4,5,6 3 Leblon 1 null 4 Gavea 2 4 5 Humaita 2 null 6 Barra 4 null 

I did this by creating a function that retrieves the STUFF () of a region’s row, but when I select all regions from a country, for example, the query becomes really, really slow, since I execute a function to get the region sons for each region.

Does anyone know how to do this optimally?

Function that "retrieves all identifiers as a string":

I meant that the function returns all the sub-band identifiers as a comma-separated string. Function:

 CREATE FUNCTION getSubRegions (@RegionId int) RETURNS TABLE AS RETURN( select stuff((SELECT CAST( wine_reg.wine_reg_id as varchar)+',' from (select wine_reg_id , wine_reg_name , wine_region_superior from wine_region as t1 where wine_region_superior = @RegionId or exists ( select * from wine_region as t2 where wine_reg_id = t1.wine_region_superior and ( wine_region_superior = @RegionId ) ) ) wine_reg ORDER BY wine_reg.wine_reg_name ASC for XML path('')),1,0,'')as Sons) GO 

Answer 1

When we used these combined lists in the database, we used a similar approach to what you do first

then when we were looking for speed

 we made them into CLR functions http://msdn.microsoft.com/en-US/library/a8s4s5dz(v=VS.90).aspx 

and now our database is only responsible for storing and retrieving data

 this sort of thing will be in our data layer in the application