[Challenge] 組織圖

Beyond Relational TSQL Challenge 8

This challenge is more about processing HIERARCHIES. SQL Server 2008 introduced a new data type HIERARCHYID which is pretty good for processing hierarchies. However, the problem explained in this challenge should be fixed without using the HIERARCHYID data type.

The problem is all about identifying all the employees directly or indirectly reporting to a given manager. Here is the organization chart of my fictitious company.

Jacob
    Jess
    Steve
        John
    Bob
        Michael
        Paul
Rui
    Smith
        Lana
        Johnson
    Bobbey
        Mic
        Stev
Jacobson
    Steffi
        Paulson
    Bracha
        Jessica

he challenge is to write a query that can take a Manager Name as a parameter and list all the employees reporting to that manager, directly or indirectly. If the parameter contains “Smith” the query should return:

Smith
    Lana
    Johnson

If the parameter passed is ‘Jacob’, the query should return:

Jacob
    Jess
    Steve
        John
    Bob
        Michael
        Paul

Your query should ideally look like the following:

DECLARE @manager VARCHAR(20)
SELECT @manager = 'Smith'
-- Your query here:

This query is pretty easy to write using a recursive CTE. To make the query complex, so that it will meet the complexity level expected for the ‘TSQL Challenges’ series, I am adding the following restriction.

“The query should be written using a recursive CTE, but the filter for "@manager” should not be applied inside the CTE”

• 規則：
1. The query should run on SQL Server 2005 and 2008
2. Write a single query that returns the results. No temp tables, functions etc should be used
3. Use 4 spaces for indentation while generating the output hierarchy
4. Siblings within a parent node may be sorted either by Name or by ID (I have sorted them by ID in the sample given above)

• 個人解法：

DECLARE @Employees TABLE (EmpID INT, EmpName VARCHAR(20), ReportsTo INT)
INSERT INTO @Employees(EmpID, EmpName, ReportsTo)
    SELECT 1, 'Jacob', NULL UNION ALL
    SELECT 2, 'Rui', NULL UNION ALL
    SELECT 3, 'Jacobson', NULL UNION ALL
    SELECT 4, 'Jess', 1 UNION ALL
    SELECT 5, 'Steve', 1 UNION ALL
    SELECT 6, 'Bob', 1 UNION ALL
    SELECT 7, 'Smith', 2 UNION ALL
    SELECT 8, 'Bobbey', 2 UNION ALL
    SELECT 9, 'Steffi', 3 UNION ALL
    SELECT 10, 'Bracha', 3 UNION ALL
    SELECT 11, 'John', 5 UNION ALL
    SELECT 12, 'Michael', 6 UNION ALL
    SELECT 13, 'Paul', 6 UNION ALL
    SELECT 14, 'Lana', 7 UNION ALL
    SELECT 15, 'Johnson', 7 UNION ALL
    SELECT 16, 'Mic', 8 UNION ALL
    SELECT 17, 'Stev', 8 UNION ALL
    SELECT 18, 'Paulson', 9 UNION ALL
    SELECT 19, 'Jessica', 10

DECLARE @manager VARCHAR(20)
SELECT @manager = 'Smith' -- 輸入 manager 名字
 
;
WITH CTE AS
(
  SELECT
    EmpID , 
    EmpName ,
    ReportsTo , 
    CAST(NULL AS VARCHAR(100))AS Manager , -- 找出 manager 名稱
    0 AS lvl ,
    CAST(EmpName AS VARCHAR(100)) AS Tree , -- 名字 Tree 圖
    CAST(EmpName AS VARCHAR(100)) AS TreePath -- 名字 Tree 圖路徑 
  FROM @Employees
  WHERE ReportsTO IS NULL
  UNION ALL
  SELECT
    E.EmpID , 
    E.EmpName ,
    E.ReportsTO , 
    CAST(T.EmpName AS VARCHAR(100)),
    lvl + 1 ,
    CAST(REPLICATE(SPACE(4),lvl + 1) + E.EmpName AS VARCHAR(100)) , -- 根據題目需求，四個空格
    CAST(T.TreePath + '_' + E.EmpName AS VARCHAR(100)) -- 把名字串接起來
  FROM CTE AS T
    JOIN @Employees AS E ON T.EmpID = E.ReportsTo
)
SELECT Tree
FROM CTE
WHERE EmpName = @manager 
  OR Manager = @manager
ORDER BY TreePath

• 個人邏輯說明：

重點在於Tree 路徑的產生，才可以正確地顯示父子關係。

參考資料：
• TSQL Challenge 8 
• Winner Solution：1、2、 3