T-SQL recursive parent child select with leveled columns

Question

My apologies for asking a question that has probably already been answered but I'm really struggling to understand how to correctly get what I need out of my database. I have a primavera P6 database which I am trying to get usable WBS codes out of. However, because I am building this into a reporting dashboard I need to do more than just concatenate parent/child values. My question will be in two parts, but to understand my question here is the basic model of the database.

wbs_id    wbs_short_name    parent_wbs_id
1         CONTR             null
2         RET               null
3         PRC               1
4         FEE               1
5         PRC               2
6         FEE               2
7         100               3
8         110               4
9         200               5
10        210               6

My first question is how can I write a query which will give me each different "level" of wbs_short_name in separate leveled columns? The end result should look like this:

Level_1    Level_2     Level_3    Level_4
CONTR      PRC         100        null
CONTR      FEE         110        null
RET        PRC         200        null
RET        FEE         210        null

I've added a level_4 because in the actual database the levels extend down to about 7 levels in, but that would overcomplicate this example. My second question is (I expect) much simpler, is it then possible to add an extra ending column which concatenates and combines all of these levels into one single WBS code? That would look like:

Level_1    Level_2     Level_3    WBS
CONTR      PRC         100        CONTRPRC100
CONTR      FEE         110        CONTRPRC110
RET        PRC         200        RETPRC200
RET        FEE         210        RETPRC210

I need the level columns for slicer controls on my dashboard and I need the WBS to be able to construct a link table which will allow me to join my P6 database to my other data sources which use these WBS codes. Thanks for any and all help given.

You have two distinct techniques going on to make this work. First is a recursive cte to get the data. Then you need to use FOR XML to concatenate values from the rows. We can help but need some information from you first. Here is a great place. to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — Sean Lange, Apr 25 '16 at 16:25

score 0 · Answer 1 · answered Apr 26 '16 at 09:10

I figured it out in the end, here's how I did it:

SELECT l1.wbs_short_name l1_wbs_short_name,
    l2.wbs_short_name l2_wbs_short_name,
    l3.wbs_short_name l3_wbs_short_name,
    l4.wbs_short_name l4_wbs_short_name,
    l5.wbs_short_name l5_wbs_short_name,
    l6.wbs_short_name l6_wbs_short_name,
    CONCAT(l1.wbs_short_name, l2.wbs_short_name, l3.wbs_short_name) as WBS,
    l3.wbs_id
FROM PROJWBS l1 
    LEFT JOIN PROJWBS l2 ON l2.parent_wbs_id = l1.wbs_id
    LEFT JOIN PROJWBS l3 ON l3.parent_wbs_id = l2.wbs_id
    LEFT JOIN PROJWBS l4 ON l4.parent_wbs_id = l3.wbs_id
    LEFT JOIN PROJWBS l5 ON l5.parent_wbs_id = l4.wbs_id
    LEFT JOIN PROJWBS l6 ON l6.parent_wbs_id = l5.wbs_id
WHERE l1.parent_wbs_id = 1
    AND l3.wbs_id IS NOT NULL
ORDER BY CONCAT(l1.wbs_short_name, l2.wbs_short_name, l3.wbs_short_name)

T-SQL recursive parent child select with leveled columns

1 Answers1