SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


self joining a table to get Parent Child relationship


self joining a table to get Parent Child relationship

Author
Message
FTdenali
FTdenali
SSC-Addicted
SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)

Group: General Forum Members
Points: 475 Visits: 572
Hi,

I need some assistance with this regard. I am trying to figure out how i can Join a table to itself to get parent child relationship results, Left join works but it's giving me good reults....How can i do a CTE for that. Or some sort of solution.
Here is the table i need to self joing below:

SELECT [Dim_CostCentreN_SK]
,[Dim_CostCentreN_Code]
,[Dim_CostCentreN_Desc]
,[Dim_CostCentreN_ClientFacing]
,[Dim_CostCentreN_Level01BK]
,[Dim_CostCentreN_Level01Desc]
,[Dim_CostCentreN_Level02BK]
,[Dim_CostCentreN_Level02Desc]
,[Dim_CostCentreN_Level03BK]
,[Dim_CostCentreN_Level03Desc]
,[Dim_CostCentreN_Level04BK]
,[Dim_CostCentreN_Level04Desc]
,[Dim_CostCentreN_Level05BK]
,[Dim_CostCentreN_Level05Desc]
,[Dim_CostCentreN_Level06BK]
,[Dim_CostCentreN_Level06Desc]
,[Dim_CostCentreN_Level07BK]
,[Dim_CostCentreN_Level07Desc]
,[Dim_CostCentreN_Level08BK]
,[Dim_CostCentreN_Level08Desc]
,[Dim_CostCentreN_Level09BK]
,[Dim_CostCentreN_Level09Desc]
,[Dim_CostCentreN_Level10BK]
,[Dim_CostCentreN_Level10Desc]
,[Dim_CostCentreN_ActiveKey]
,[Dim_CostCentreN_DateStart]
,[Dim_CostCentreN_DateEnd]
FROM [BI_Blueprint_EDW].[dbo].[Dim_CostCentre_N]
GO
Jason-299789
Jason-299789
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2151 Visits: 3232
I'm not sure you can do it in a CTE, can to you provide some sample data for us to play around with.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
FTdenali
FTdenali
SSC-Addicted
SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)

Group: General Forum Members
Points: 475 Visits: 572
SELECT TOP 1000 [Dim_CostCentre_SK]
,[Dim_CostCentre_BK]
,[Dim_CostCentre_ParentBK]
,[Dim_CostCentre_Desc]
FROM [BI_Blueprint_EDW].[dbo].[Dim_CostCentre]
----------------------------------------------------------------------------------

Dim_CostCentre_SK Dim_CostCentre_BK Dim_CostCentre_ParentBK Dim_CostCentre_Desc
1 10011070 dsst159 Actuarial & Insurance Solutions
JHB
2 10011110 dsst159 Actuarial &

Insurance
Solutions 2 JHB
3 10041070 JHsw160 Actuarial &
Insurance Solutions CTN
4 1026000 PrdL755 Regional Leader PTA
5 1026001 PrASL123 Regional Leader PTA
6 1046000 CTNd443 Regional Leader CTN


I i need to make a Parent child relationship using this table joining Costentre_ParentBK and Costcenter
Jason-299789
Jason-299789
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2151 Visits: 3232
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/

_________________________________________________________________________
SSC Guide to Posting and Best Practices
FTdenali
FTdenali
SSC-Addicted
SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)

Group: General Forum Members
Points: 475 Visits: 572
Hi I get this error
Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
Jason-299789
Jason-299789
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2151 Visits: 3232
Sorry I forgot to add the Level into the second Select statement which should read like this


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
,p.Level+1 Level
FROM Dim_CostCentre d
JOIN HierarchyBuilder_Cte p on d.Dim_CostCentre_ParentBK=p.Dim_CostCentre_SK



_________________________________________________________________________
SSC Guide to Posting and Best Practices
FTdenali
FTdenali
SSC-Addicted
SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)

Group: General Forum Members
Points: 475 Visits: 572
Hi,

Now i get
Msg 240, Level 16, State 1, Line 1
Types don't match between the anchor and the recursive part in column "Dim_CostCentre_Desc" of recursive query "HierarchyBuilder_Cte".
Jason-299789
Jason-299789
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2151 Visits: 3232
What Data type is the Description column, nvarchar() or varchar()?

_________________________________________________________________________
SSC Guide to Posting and Best Practices
FTdenali
FTdenali
SSC-Addicted
SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)

Group: General Forum Members
Points: 475 Visits: 572
varchar(255)
Jason-299789
Jason-299789
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2151 Visits: 3232
That shouldnt make a difference, but you can wrap a CAST or Convert around both levels in the union to force it into a varchar.

how wide is the column likely to get, as you might need to use a varchar(max).

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search