SQL Server 2008 R2 - 递归 SQL - 这可能吗？

Question

我有下表:

| Article-Material1 | Article-Material2 |
|-------------------|-------------------|
| article001        | article002        |
| article001        | article003        |
| article001        | material001       |
| material001       |                   |
| article002        | article004        |
| article002        | material002       |
| material002       |                   |
| article003        | material003       |
| material003       |                   |
| article004        | material004       |
| material004       |                   |
| article005        | article010        |
| article005        | article011        |
| article005        | material001       |
| article010        | material005       |
| material005       |                   |
| article011        | article012        |
| article011        | material004       |
| article011        | material006       |
| material006       |                   |
| article012        | material002       |
| article012        | material007       |
| material007       |                   |

我想实现这样的输出:

article001
    |- article002
        |- article004
            |- material004
        |- material002
    |- article003
        |- material003
    |- material001
article005
    |- article010
        |- material005
    |- article011
        |- article012
            |- material002
            |- material007
        |- material004
        |- material006
    |- material001

我不知道这是否可以用 SQL 实现。如果这不可能，我还能尝试什么来朝着正确的方向前进？

Answer 1

出于演示目的，您可以根据我之前的一篇文章使用以下代码。
SQL Challenge/Puzzle: How to create an ASCII art hierarchy tree with an SQL query?

with        h (id,pid)
            as
            (
                select      [Article-Material2] as id
                           ,[Article-Material1] as pid

                from        mytable

                where       [Article-Material2] is not null

                union all

                select      distinct
                            [Article-Material1]     as id
                           ,null                    as pid

                from        mytable

                where       [Article-Material1] not in (select [Article-Material2] from mytable where [Article-Material2] is not null)
            )       

           ,last_sibling (id)
            as
            (
                select      max (id)
                from        h
                group by    pid
            )

           ,tree (id,branch,path)
            as
            (
                select      h.id
                           ,cast ('' as varchar(max))
                           ,cast (h.id as varchar(max))

                from        h

                where       h.pid is null

                union all

                select      h.id
                           ,t.branch + case when (select 1 from last_sibling ls where ls.id = t.id) = 1 then ' ' else '|' end + '    '
                           ,t.path + '_' + h.id

                from                    tree            t

                            join        h

                            on          h.pid =
                                        t.id
            )

           ,vertical_space (n)
            as
            (
                select      1

                union all

                select      vs.n + 1
                from        vertical_space  vs
                where       vs.n < 2
            )

select      t.branch + case vs.n when 1 then '|____' + ' ' + t.id else '|' end

from                    tree            t

            cross join  vertical_space  vs

order by    t.path
           ,vs.n desc

option      (maxrecursion 0)
;

---

|
|____ article001
|    |
|    |____ article002
|    |    |
|    |    |____ article004
|    |    |    |
|    |    |    |____ material004
|    |    |
|    |    |____ material002
|    |
|    |____ article003
|    |    |
|    |    |____ material003
|    |
|    |____ material001
|
|____ article005
     |
     |____ article010
     |    |
     |    |____ material005
     |
     |____ article011
     |    |
     |    |____ article012
     |    |    |
     |    |    |____ material002
     |    |    |
     |    |    |____ material007
     |    |
     |    |____ material004
     |    |
     |    |____ material006
     |
     |____ material001