This query produces a SQL of the Employee Tree. It can be used as is, but it is often useful as a basis for other Employee based SQL's where the data should be displayed in tree format.

Notes

None. This is a selection query and no data is modified in the running of it.

DECLARE @ShowInactive bit;
--SET @ShowInActive = ;
SET @ShowInactive = 1;
DECLARE @Spacing INT;
SET @Spacing = 4;
SELECT 
  ROW_NUMBER() OVER(ORDER BY FormattedPath) ROW,
  *
FROM
(
	SELECT 
	  CASE WHEN Cat5.GroupName IS NOT NULL THEN SPACE(@Spacing * 4) 
		ELSE CASE WHEN Cat4.GroupName IS NOT NULL THEN SPACE(@Spacing * 3)  
		ELSE CASE WHEN Cat3.GroupName IS NOT NULL THEN SPACE(@Spacing * 2) 
		ELSE CASE WHEN Cat2.GroupName IS NOT NULL THEN SPACE(@Spacing * 1) 
		ELSE CASE WHEN Cat1.GroupName IS NOT NULL THEN SPACE(@Spacing * 0) 
		END END END END END +
 		CASE WHEN IsGroup=1 THEN '' ELSE SPACE(@Spacing * 1)END +
		COALESCE(Employee.LastName+', '+Employee.FirstName, Cat5.GroupName, Cat4.GroupName, Cat3.GroupName, Cat2.GroupName, Cat1.GroupName) AS FormattedName,
 
	  COALESCE(Employee.LastName+' '+Employee.FirstName, Cat5.GroupName, Cat4.GroupName, Cat3.GroupName, Cat2.GroupName, Cat1.GroupName) AS NodeName,
	  COALESCE(Employee.ID, Cat5.ID, Cat4.ID, Cat3.ID, Cat2.ID, Cat1.ID) AS NodeID,
	  COALESCE(Employee.ClasstypeID, Cat5.ClasstypeID, Cat4.ClasstypeID, Cat3.ClassTypeID, Cat2.ClasstypeID, Cat1.ClasstypeID) AS NodeClassTypeID,  
	  COALESCE(Employee.IsActive, Cat5.IsActive, Cat4.IsActive, Cat3.IsActive, Cat2.IsActive, Cat1.IsActive) AS IsActive,
	  IsGroup,
	  COALESCE(Cat5.ID - Cat5.ID + 5, Cat4.ID - Cat4.ID + 4, Cat3.ID - Cat3.ID + 2, Cat2.ID - Cat2.ID + 2, Cat1.ID - Cat1.ID + 1, 0) AS Depth,
	  COALESCE(Cat5.ID, Cat4.ID, Cat3.ID, Cat2.ID, Cat1.ID) AS ParentID,
	  COALESCE(Cat5.ClassTypeID, Cat4.ClassTypeID, Cat3.ClassTypeID, Cat2.ClassTypeID, Cat1.ClassTypeID) AS ParentClassTypeID,
 
	  COALESCE(Employee.LastName+', '+Employee.FirstName, '') AS EmployeeName,
	  COALESCE(Cat1.GroupName, '') AS Category1Name,
	  COALESCE(Cat2.GroupName, '') AS Category2Name,
	  COALESCE(Cat3.GroupName, '') AS Category3Name,
	  COALESCE(Cat4.GroupName, '') AS Category4Name,
	  COALESCE(Cat5.GroupName, '') AS Category5Name,
 
	  Employee.ID AS EmployeeID,
	  Cat1.ID AS Category1ID,
	  Cat2.ID AS Category2ID,
	  Cat3.ID AS Category3ID,
	  Cat4.ID AS Category4ID,
	  Cat5.ID AS Category5ID,
 
	  COALESCE('1-'+Cat1.GroupName + '\', '') +
		coalesce('2-'+Cat2.GroupName + '\', '') + 
		coalesce('3-'+Cat3.GroupName + '\', '') +
		coalesce('4-'+Cat4.GroupName + '\', '') +
		coalesce('5-'+Cat5.GroupName + '\', '') +
		coalesce(Employee.LastName+', '+Employee.FirstName, '') as FormattedPath
 
	from EmployeeGroup as Cat1
	JOIN (SELECT 1 AS IsLevel1 UNION SELECT 0) Level1 ON Cat1.ID > 0 and Cat1.ParentID is NULL -- create two rows for each of the above
	left join EmployeeGroup as Cat2 on Cat2.ParentID = Cat1.ID and IsLevel1 = 0 
	JOIN (SELECT 1 AS IsLevel2 UNION SELECT 0) Level2 ON 1=1 -- create two rows for each of the above
 
	left join EmployeeGroup as Cat3 on Cat3.ParentID = Cat2.ID and IsLevel1 = 0 and IsLevel2 = 0 
	JOIN (SELECT 1 AS IsLevel3 UNION SELECT 0) Level3 ON 1=1 -- create two rows for each of the above
 
	left join EmployeeGroup as Cat4 on Cat4.ParentID = Cat3.ID and IsLevel1 = 0 and IsLevel2 = 0 and IsLevel3 = 0 
	JOIN (SELECT 1 AS IsLevel4 UNION SELECT 0) Level4 ON 1=1 -- create two rows for each of the above
 
	left join EmployeeGroup as Cat5 on Cat5.ParentID = Cat4.ID and IsLevel1 = 0 and IsLevel2 = 0 and IsLevel3 = 0 and IsLevel4 = 0
	JOIN (SELECT 1 AS IsLevel5 UNION SELECT 0) Level5 ON 1=1 -- create two rows for each of the above
 
    JOIN (SELECT 1 AS IsGroup UNION SELECT 0) Temp ON 1=1 -- create two rows for each of the above
	left join Employee on Employee.GroupID = coalesce(Cat5.ID, Cat4.ID, Cat3.ID, Cat2.ID, Cat1.ID) and IsGroup = 0 and coalesce(Employee.ID,1) > 0
 
	where (IsLevel1 + IsLevel2 + IsLevel3 + IsLevel4 + IsLevel5) = 1
	  and (   (IsLevel1 = 1)
	       or (IsLevel2 = 1 and Cat2.ID IS NOT NULL)
	       or (IsLevel3 = 1 and Cat3.ID IS NOT NULL)
	       or (IsLevel4 = 1 and Cat4.ID IS NOT NULL)
	       or (IsLevel5 = 1 and Cat5.ID IS NOT NULL) )
 
) EmployeeTree
WHERE ((IsActive = 1) or (@ShowInactive = 1))
order by Row

• Entered : 1/8/2010
• Version : Control 4.0+