Self recursive SQL Server database table to create a tree structure, create model for this , and using this data in Devexpress

Question

I have location and employee database tables. I want to show all locations (from root location to all child locations) and items that exist in these locations. I decided to use Devexpress reporting to create a report with this info. This project is implemented with ASP.NET MVC. I need help, can you help me with this project?

My Location table in SQL Server looks like:

• parentid
• childid
• name

and my Employee table

• id
• locationid (for locations table)
• name
• gender
• professionid (connected to another profession table:1: executive, 2:technician ...etc.)

I want to create a result in reporting like that:

Apple
    Apple America
          Apple New York
               ...
                  Name1  Surname1    gender1  profession1   ... 
                  Name2  Surname2    gender2  profession2   ... 

           Apple Boston 
                  Name3  Surname3   gender3  profession3  ... 

What model should I create to implement this structure can you give me some advice, I retrieve data in a wrong way.

Answer 1

For getting recursive data you can use recursive common table expression like this:

DECLARE @DataSource TABLE
(
    [parentid] INT
   ,[childid] INT
   ,[name] VARCHAR(16)
);

INSERT INTO @DataSource ([parentid], [childid], [name])
VALUES (NULL, 10, 'parent A')
      ,(10, 11, 'child A1')
      ,(10, 12, 'child A2')
      ,(NULL, 13, 'parent B')
      ,(13, 14, 'sub-parent B1')
      ,(13, 15, 'sub-parent B2')
      ,(14, 16, 'child B2 - C1')
      ,(14, 17, 'child B2 - C2');

WITH RecursiveDataSource AS
(
    SELECT *
          ,1 AS [Level]
          ,ROW_NUMBER() OVER (ORDER BY(SELECT 1)) AS [FamilyID]
    FROM @DataSource 
    WHERE [parentid] IS NULL
    UNION ALL
    SELECT DS.*
          ,RDS.[Level] + 1 
          ,RDS.[FamilyID]
    FROM RecursiveDataSource RDS
    INNER JOIN @DataSource DS
        ON RDS.[childid] = DS.[parentid]
)
SELECT *
FROM RecursiveDataSource
ORDER BY [FamilyID] 
        ,[Level];

Then using these columns you can vitalize the data in the desire way.