Help needed with nested sql query

Transact-SQL
1

Hi Guys,

we are using sql server 2005 version and i needed a sql query help for this situation:

Declare @EmployeeDetails table
(parent_level int,
[level] int,
employee varchar(20),
[description] varchar(100)

)

Insert into @EmployeeDetails
Select 0,1100,'8646','employee1'
union
Select 0,1100,'8656','employee2'
union
Select 0,1100,'8666','employee3'
union
Select 0,1100,'8676','employee4'
union
Select 1100,1234,'','Insurance'
union
Select 1234,56789,'','All Records'
union
Select 56789,0,'','MAIN_FOLDER'

Select * from @EmployeeDetails

When i run the above query , I will get the below:

I would like my result set to be :

CAn you please help me with the above sql query? The tree structure is not fixed, the desired data may be found under 5 folders or 2 folders or 4 folders etc.

Thanks,
Raaj

2 
;WITH cte AS
( SELECT 
    parent_level,[level],employee, CAST('' AS VARCHAR(100)) AS EmpDescription
            ,CAST([description] AS VARCHAR(500)) AS [description]
            ,CAST('' AS VARCHAR(500)) AS locationOfRecord
  FROM    
    @EmployeeDetails AS E
  WHERE 
    E.[level] = 0 

  UNION ALL

  SELECT
    ED.parent_level,ED.[level],ED.employee , ED.[description] AS EmpDescription
        ,CAST(R.[description] + '/'+ED.[description] AS VARCHAR(500))
        ,CAST(R.[description] AS VARCHAR(500))
  FROM    
    cte AS R
    INNER JOIN @EmployeeDetails AS ED
    ON R.[parent_level] = ED.[level]
  WHERE 
    --ED.[parent_level] = 0
     ED.[level] <> 0
)

SELECT parent_level,[level],employee,EmpDescription,locationOfRecord
FROM cte 
WHERE parent_level  = 0


parent_level    level    employee    EmpDescription    locationOfRecord
0    1100    8646    employee1    MAIN_FOLDER/All Records/Insurance
0    1100    8656    employee2    MAIN_FOLDER/All Records/Insurance
0    1100    8666    employee3    MAIN_FOLDER/All Records/Insurance
0    1100    8676    employee4    MAIN_FOLDER/All Records/Insurance
3

That worked.
Thanks Stepson.

4

Welcome!