Recursion to get complete hierarchy

Question

I have two tables, one stores a techtree and has the following structure:

**TECHTREE TABLE**
ID - int
ParentID - int (relational to ID)
name - varchar

and a datatable 'UserSkills' with the following structure:

**USERSKILLS TABLE**
TECHID - int (relational to TECHTREE.ID)
SKILLLEVEL - varchar
LOGIN - varchar

Given is the following techtree data

ID  ParentID  Name
1   24        Microsoft
2   1         Server
3   1         Hyper-V
4   24        VMWare
5   4         vSphere
7   2         Betriebssystem
8   2         AD, DNS, DHCP
9   2         PKI
14  5         4.x
15  5         5.x
16  4         VDI/View
17  4         SRM
18  24        Symantec
19  18        Backup Exec
20  19        2010
21  19        2012
22  18        SEP
23  24        Citrix
24  NULL      Data Center SW
25  NULL      Data Center HW
26  NULL      IPT
27  NULL      Networking
28  NULL      Security
29  NULL      Misc

and following userskills data

TechID   SkillLevel     Login
7        Expert         mike
8        Basics         mike
9        Basics         peter   
67       Expert         peter
31       Expert         peter
32       Support        chris
33       Expert         peter
34       Expert         chris
35       Expert         adam
36       Support        adam
65       Support        adam
66       Expert         tom
78       Expert         tom
75       Basics         tom
76       Expert         tom
77       Expert         tom

Now i want to get the rows from userskill table including the full hierarchy from the techtree table. The result should look like

ID  LVL0              LVL1          LVL2      LVL3            LVL4        LVL5      LVL6    Skilllevel
7   Data Center SW    Microsoft     Server    OS                                            Expert
8   Data Center SW    Microsoft     Server    AD, DNS, DHCP                                 Basics
9   Data Center SW    Microsoft     Server    PKI                                           Basics
67  Networking                                                                              Expert                                
31  Networking                                                                              Expert                                

Does anybody knows how this can be achieved in sql?

Answer 1

I actually just made a solution that will help you with this. I wanted to make a string with a 'path' for my organisationtree. So I made a recursive CTE to map the levels in the tree:

;with orgSublevels as (
select 
name
  ,cast('head' as nvarchar(50)) as parent
  ,ID
  ,ParentID
  ,1 as levelof
  ,cast(ID as nvarchar(max)) as orgPath
from Tree
union all
select 
name
  ,parent.name as nvarchar(50)) as parent
  ,ID
  ,ParentID
  ,levelof + 1 as levelof
  ,cast(o.orgPath as nvarchar(max)) + ', '+ cast(tree.ID as nvarchar(max)) as orgPath
from orgSublevels o
join Tree on tree.ID = o.parentID
)

Now you have the levels and the paths of your organisationtree. Using a (dynamic) pivot, you can made a table with the levels as columns. Hope that helps!

Answer 2

The sample data is still broken. There is no ID 67 or 31 now. This is getting close though

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

I did not use recursive CTEs because you have a fixed number of levels.

WITH CTE AS
(
    SELECT
        TT.ID, TT.ParentID, TT.Name, US.SkillLevel
    FROM
        dbo.TechTree TT
        LEFT JOIN
        dbo.UserSkills US ON TT.ID = US.TechID
)
SELECT 
    COALESCE(c6.ID, c5.ID, c4.ID, c3.ID, c2.ID, c1.ID, c0.ID) AS ID,
    c0.Name AS LVL0,
    c1.Name AS LVL1,
    c2.Name AS LVL2,
    c3.Name AS LVL3,
    c4.Name AS LVL4,
    c5.Name AS LVL5,
    c6.Name AS LVL6,
    COALESCE(c6.SkillLevel, c5.SkillLevel, c4.SkillLevel, c3.SkillLevel, c2.SkillLevel, c1.SkillLevel, c0.SkillLevel) AS SkillLevel
FROM 
    CTE c0 
    LEFT JOIN
    CTE c1 ON c0.ID = c1.ParentID 
    LEFT JOIN
    CTE c2 ON c1.ID = c2.ParentID
    LEFT JOIN
    CTE c3 ON c2.ID = c3.ParentID
    LEFT JOIN
    CTE c4 ON c3.ID = c4.ParentID
    LEFT JOIN
    CTE c5 ON c4.ID = c5.ParentID
    LEFT JOIN
    CTE c6 ON c5.ID = c6.ParentID
WHERE 
    c0.ParentID IS NULL