TSQL层次结构

Question

我正试图在我的表上创建一个CTE，以吸引员工的层次结构。

我有一个起点，那就是“主任”，我想找到每一个向他们下属的人汇报的人。

以下是我到目前为止所拥有的：

;WITH EmpTable_CTE (FirstName, LastName, QID, Email) AS
    (
        SELECT      FirstName,
                    LastName,
                    QID,
                    Email
        FROM        EmployeeTable E
        WHERE       QID = '12345'

        UNION ALL

        SELECT      E.FirstName,
                    E.LastName,
                    E.QID,
                    E.Email
        FROM        EmployeeTable E
        INNER JOIN  EmpTable_CTE AS E2 ON E.MgrQID = E2.QID
    )
    SELECT * FROM EmpTable_CTE 

这似乎可以为我提供一份员工名单，但并没有“等级”。

如何使用FOR XML创建我正在寻找的层次结构？

<Director>Bob Smith</Director>
    <Direct>Jim Smith</Direct>
        <Direct>Employee 1</direct>
        <Direct>Employee 2</direct>
        <Direct>Employee 3</direct>
    <Direct>Bob Jones</Direct>
        <Direct>Employee 1</direct>
        <Direct>Employee 2</direct>
        <Direct>Employee 3</direct>
            <Direct>Employee A</direct>

我确信这只是一个把FOR XML线放在某个地方的问题，但我无法完全弄清楚。

更新:下面是示例数据的：

http://sqlfiddle.com/#!6/a48f6/1

这就是我所期望的数据来自小提琴的方式：

<Director>Jim Jones</Director>
    <Direct>Bob Jones</Direct>
        <Direct>Jake Jones</Direct>
        <Direct>Smith Jones</Direct>
            <Direct>Carl Jones</Direct>
                <Direct>Bobby Jones</Direct>
    <Direct>Danny Jones</Direct>
        <Direct>Billy Jones</Direct>

Answer 1

部分困难在于您提供的XML结构--如果您将其传递到解析器中，那么它将是平面的，并且在不将First和Last填充到属性中的情况下运行下面的进程结果，使得节点以混合内容(节点位于同一级别上的文本)出现。

于是，我去搜索并找到了这个小宝石在SE上。根据您的需要调整它，并从您的表中添加一些字段作为属性，我想出了以下内容：

CREATE FUNCTION dbo.EmpHierarchyNode(@QID int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT 
BEGIN RETURN 
  (SELECT QID AS "@ID", Email AS "@Email",
    FirstName + ' ' + LastName AS "@Name",
      CASE WHEN MgrQID = @QID
      THEN dbo.EmpHierarchyNode(QID)
      END
   FROM dbo.EmployeeTable
   WHERE MgrQID = @QID
   FOR XML PATH('Direct'), TYPE)
END

SELECT  QID AS "@ID", Email AS "@Email",
    FirstName + ' ' + LastName AS "@Name",
    dbo.EmpHierarchyNode(QID)
FROM dbo.EmployeeTable
WHERE MgrQID IS NULL
FOR XML PATH('Director'), TYPE

本质上，这在层次结构中向下遍历，递归地调用自己。如果您的输出是针对XML的，那么CTE是不够的。使用这个，以及我能够收集到的样本数据，我得到了以下结果：

<Director ID="1" Email="bsmith@someCompany.com" Name="Bob Smith">
  <Direct ID="2" Email="jsmith@someCompany.com" Name="Jim Smith">
    <Direct ID="4" Email="e1@someCompany.com" Name="Employee 1" />
    <Direct ID="5" Email="e2@someCompany.com" Name="Employee 2" />
    <Direct ID="7" Email="e4@someCompany.com" Name="Employee 4" />
  </Direct>
  <Direct ID="3" Email="bjones@someCompany.com" Name="Bob Jones">
    <Direct ID="6" Email="e3@someCompany.com" Name="Employee 3" />
    <Direct ID="8" Email="e5@someCompany.com" Name="Employee 5" />
    <Direct ID="9" Email="e6@someCompany.com" Name="Employee 6" />
  </Direct>
</Director>

希望这能有所帮助。

编辑:最后一分钟SQLFiddle实例。

Answer 2

看看它是否符合你的要求：

;WITH EmpTable_CTE (FirstName, LastName, QID, Email) AS
    (
        SELECT      FirstName,
                    LastName,
                    QID,
                    Email
        FROM        EmployeeTable E
        WHERE       QID = 1
        UNION ALL
        SELECT      E.FirstName,
                    E.LastName,
                    E.QID,
                    E.Email
        FROM        EmployeeTable E
        INNER JOIN  EmpTable_CTE AS E2
        ON          E.MgrQID = E2.QID
    )

    SELECT LastName + ', ' + FirstName FROM EmpTable_CTE FOR XML PATH('Direct'), ROOT('Director'), TYPE