Sorry I think I missunderstood, you want to generate the data from a Parent Child hierarchy, in order to populate that table, correct?

there are a few questions, as there are a couple of ways of doing this,

1) is this going to be ragged hierarchy?

2) Can data be posted against any level in the hierarchy?

the recursive CTe is probably the easiest way to implement this, the first and simplest method it to build a delimited hierarchy path with the nodes concatinated, eg Level1\Level2\Level3 then run a string splitter to split this into the sperate fields.

The Recursive Part is simple, something like this should work

WITH HierarchyBuilder_Cte

AS

(

/*

Root Node Anchor

*/

Select Dim_CostCentre_SK

,Dim_CostCentre_ParentBK

,Dim_CostCentre_Desc

,0 Level

from Dim_CostCentre

Where Dim_CostCentre_ParentBK IS NULL

/*

Get all the children

*/

UNION ALL

SELECT d.Dim_CostCentre_SK

,d.Dim_CostCentre_ParentBK

,p.DimcostCentre_Desc+'\'+d.Dim_CostCentre_Desc

FROM Dim_CostCentre d

JOIN HierarchyBuilder_Cte p on d.Dim_CostCentre_ParentBK=p.Dim_CostCentre_SK

)

Select *

from HierarchyBuilder_Cte

There might be a problem with the join in the CTE that builds the list.

A good string splitter can be found here http://www.sqlservercentral.com/articles/Tally+Table/72993/