November 25, 2012 at 11:54 pm
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
November 26, 2012 at 1:10 am
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
November 26, 2012 at 1:45 am
SELECT TOP 1000 [Dim_CostCentre_SK]
,[Dim_CostCentre_BK]
,[Dim_CostCentre_ParentBK]
,[Dim_CostCentre_Desc]
FROM [BI_Blueprint_EDW].[dbo].[Dim_CostCentre]
----------------------------------------------------------------------------------
Dim_CostCentre_SKDim_CostCentre_BKDim_CostCentre_ParentBK Dim_CostCentre_Desc
110011070dsst159 Actuarial & Insurance Solutions
JHB
210011110dsst159 Actuarial &
Insurance
Solutions 2 JHB
310041070JHsw160 Actuarial &
Insurance Solutions CTN
41026000 PrdL755 Regional Leader PTA
51026001 PrASL123 Regional Leader PTA
61046000 CTNd443 Regional Leader CTN
I i need to make a Parent child relationship using this table joining Costentre_ParentBK and Costcenter
November 26, 2012 at 2:18 am
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
November 26, 2012 at 3:01 am
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.
November 26, 2012 at 3:20 am
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
November 26, 2012 at 4:56 am
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".
November 26, 2012 at 5:06 am
What Data type is the Description column, nvarchar() or varchar()?
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 26, 2012 at 5:24 am
varchar(255)
November 26, 2012 at 5:53 am
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
November 26, 2012 at 6:50 am
ok it works but no results
November 26, 2012 at 7:23 pm
If the Parent/Child relationship has been checked for "cycles" and there are none, the you might be interested in the following two related articles on a couple of ways to covert such Adjacency Lists into some blazingly fast structures.
http://www.sqlservercentral.com/articles/Hierarchy/94040/
http://www.sqlservercentral.com/articles/T-SQL/94570/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply