SQL Server 2008 R2 - Recursive SQL - Is this possible?

Question

I have the following table:

| 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       |                   |

And i want to achieve an ouput like this:

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

I have no idea if this is possible with SQL. If it is not possible, what else could I try to get in the right direction?

Answer 1

For presentation purpose you can use the following code, based on a previous post of mine.
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