Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

self joining a table to get Parent Child relationship Expand / Collapse
Author
Message
Posted Sunday, November 25, 2012 11:54 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 30, 2014 2:16 AM
Points: 180, Visits: 527
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
Post #1388440
Posted Monday, November 26, 2012 1:10 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Post #1388459
Posted Monday, November 26, 2012 1:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 30, 2014 2:16 AM
Points: 180, Visits: 527
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
Post #1388465
Posted Monday, November 26, 2012 2:18 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Post #1388471
Posted Monday, November 26, 2012 3:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 30, 2014 2:16 AM
Points: 180, Visits: 527
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.
Post #1388489
Posted Monday, November 26, 2012 3:20 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Post #1388497
Posted Monday, November 26, 2012 4:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 30, 2014 2:16 AM
Points: 180, Visits: 527
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".
Post #1388536
Posted Monday, November 26, 2012 5:06 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
What Data type is the Description column, nvarchar() or varchar()?

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1388543
Posted Monday, November 26, 2012 5:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 30, 2014 2:16 AM
Points: 180, Visits: 527
varchar(255)
Post #1388549
Posted Monday, November 26, 2012 5:53 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Post #1388564
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse