none
CTE tree to return tree path in seperate columns

    Question

  • Hello

    I have a great CTE query that builds a node path but now I need to modify this query to return the path on seperate columns rather than "combining" the tree nodenames into one column.

    I need to breake the below strings in to 4 parts (columns) called SITE, AREA, BUILDING, SYSTEM. Can that be done in a CTE?

    I guess I could code something with charindex etc. but would some CTE not be better?

    The seperator is the "-" and there are always 4 parts.

    The result set should look like this

    SITE AREA BUILDING SYSTEM
    NNBA NYD DIVERSE (APPS05) 09A
    NNBA 6BC VENTILATION (APPS05) 07ND
    NNBA 6BD VENTILATION (APPS05) 07NA

    NNBA -NYD-DIVERSE (APPS05)-09A
    NNBA -NYD-DIVERSE (APPS05)-09A
    NNBA -6BC-VENTILATION (APPS05)-07ND
    NNBA -6BD-VENTILATION (APPS05)-07NA
    NNBA -6BD-VENTILATION (APPS05)-07NA
    NNBA -6BC-VENTILATION (APPS05)-07NA
    NNBA -6BC-VENTILATION (APPS05)-07NA
    NNBA -6A-VENTILATION (APPS05)-07F
    NNBA -6BC-VENTILATION (APPS05)-07ND
    NNBA -8Q-VENTILATION (APPS05)-07C
    NNBA -9O-VENTILATION (APPS05)-07A
    NNBA -6BC-DIVERSE (APPS05)-09A
    NNBA -6BC-VENTILATION (APPS05)-07ND
    NNBA -9O-VENTILATION (APPS05)-07A
    NNBA -6BC-DIVERSE (APPS05)-09A
    NNBA -9O-VENTILATION (APPS05)-07A
    NNBA -6BC-VENTILATION (APPS05)-07ND
    NNBA -6A-VENTILATION (APPS05)-07F
    NNBA -8Q-VENTILATION (APPS05)-07D
    NNBA -9O-VENTILATION (APPS05)-07A

    	with TreeCTE as (
    	select NodeID, ParentID, NodeLevel, NodeName, 
    		   cast('' as varchar(max)) as NodePath,
    		   cast('' as varchar(max)) as AlarmPath
    		   ,'bd' as byg, 'sys' as syst
    	from Tree_Alarms
    	where NodeID = 2608
    	union ALL
    	select Tre.NodeID, Tre.ParentID, Tre.NodeLevel, Tre.NodeName,  
    		   cast(Tre.NodeName+ '-' + TreCTE.NodePath as varchar(max)),
    		   TreCTE.NodePath,
    		   TreCTE.byg,
    		   TreCTE.syst
    	from TreeCTE as TreCTE
    	join dbo.Tree_Alarms as Tre
    	  on Tre.NodeID  = TreCTE.ParentID )
    			select SUBSTRING(NodePath, 1, (LEN(NodePath)-1)) as AlarmPath, byg, syst
    	from TreeCTE
    	where NodeLevel = 0
    The syst and byg was just something I tried to solve the query with (but I didn't solve it)


    Henry


    • Edited by Henry64 Friday, October 18, 2013 4:04 PM
    Friday, October 18, 2013 4:02 PM

Answers

  • Hi

    I have solved it like this, building it from the tree works better than splitting a "alarmpath" column into multiple columns.

    	with TreeCTE as (
    	select NodeID, ParentID, NodeLevel, NodeName, 
    
    		   cast('' as varchar(max)) as N0, 
    		   cast('' as varchar(max)) as N1,
    		   cast('' as varchar(max)) as N2,
    		   cast('' as varchar(max)) as N3,
    		   cast(NodeName as varchar(max)) as N4,
    		   cast('' as varchar(max)) as NodePath,
    		   cast('' as varchar(max)) as AlarmPath
    		   
    	from Tree_Alarms
    	where NodeID = 2608
    	union ALL
    	select Tre.NodeID, Tre.ParentID, Tre.NodeLevel, Tre.NodeName,  
    		   
    		   N0 = CASE 
    			WHEN Tre.NodeLevel = 0 AND TreCTE.N0 = '' THEN cast (Tre.NodeName as varchar(max))
    			ELSE cast (TreCTE.N0 as varchar(max))
    			END ,
    		   
    		   N1 = CASE 
    			WHEN Tre.NodeLevel = 1 AND TreCTE.N1 = '' THEN cast (Tre.NodeName as varchar(max))
    			ELSE cast (TreCTE.N1 as varchar(max))
    			END ,
    
    		   N2 = CASE 
    			WHEN Tre.NodeLevel = 2 AND TreCTE.N2 = '' THEN cast (Tre.NodeName as varchar(max))
    			ELSE cast (TreCTE.N2 as varchar(max))
    			END ,
    		   
    		   N3 = CASE 
    			WHEN Tre.NodeLevel = 3 AND TreCTE.N3 = '' THEN cast (Tre.NodeName as varchar(max))
    			ELSE cast (TreCTE.N3 as varchar(max))
    			END ,
    
    			coalesce(cast(TreCTE.N4 as varchar(max)), cast(Tre.NodeName as varchar(max)) ) as N4,
    
    		   cast(Tre.NodeName+ '-' + TreCTE.NodePath as varchar(max)),
    		   TreCTE.NodePath
    		   
    	from TreeCTE as TreCTE
    	join dbo.Tree_Alarms as Tre
    	  on Tre.NodeID  = TreCTE.ParentID )
    			select --SUBSTRING(NodePath, 1, (LEN(NodePath)-1)) as AlarmPath, 
    			AlarmPath,
    			NodeName,
    			N0 as [SITE], N1 as [AREA], N2 as [BUILDING], N3 as [SYSTEM], N4 as [TECHADDR], NodeLevel
    	from TreeCTE
    I'm sure this could have been done more efficiently.

    Henry

    • Marked as answer by Henry64 Monday, October 21, 2013 9:27 AM
    Monday, October 21, 2013 9:26 AM

All replies

  • If the "-" appear 3 times in a string we can use PARSENAME function,

    select 
    parsename(replace('NNBA -NYD-DIVERSE (APPS05)-09A','-','.'),1),
    parsename(replace('NNBA -NYD-DIVERSE (APPS05)-09A','-','.'),2),
    parsename(replace('NNBA -NYD-DIVERSE (APPS05)-09A','-','.'),3),
    parsename(replace('NNBA -NYD-DIVERSE (APPS05)-09A','-','.'),4)


    Regards, RSingh

    • Proposed as answer by Durval Ramos Friday, October 18, 2013 5:45 PM
    Friday, October 18, 2013 4:17 PM
  • Hi RSingh

    Thanks for your suggestion, it actually works although is has some weaknesses like if any nodenames contains "." then it will mess up the result quite badly, that actually also goes for "-" as well.

    I'll have to finde another solution for this that involves "storing" the nodename from each level i it's own column while recursing the tree from level 4 to level 0. E.g. have 5 columns NN0, NN1, NN2, NN3 and NN4.

    I just cannot figure out how to "save" a value depending on the level, right now the value is overriden for each iteration.


    Henry

    Monday, October 21, 2013 8:34 AM
  • Hi

    I have solved it like this, building it from the tree works better than splitting a "alarmpath" column into multiple columns.

    	with TreeCTE as (
    	select NodeID, ParentID, NodeLevel, NodeName, 
    
    		   cast('' as varchar(max)) as N0, 
    		   cast('' as varchar(max)) as N1,
    		   cast('' as varchar(max)) as N2,
    		   cast('' as varchar(max)) as N3,
    		   cast(NodeName as varchar(max)) as N4,
    		   cast('' as varchar(max)) as NodePath,
    		   cast('' as varchar(max)) as AlarmPath
    		   
    	from Tree_Alarms
    	where NodeID = 2608
    	union ALL
    	select Tre.NodeID, Tre.ParentID, Tre.NodeLevel, Tre.NodeName,  
    		   
    		   N0 = CASE 
    			WHEN Tre.NodeLevel = 0 AND TreCTE.N0 = '' THEN cast (Tre.NodeName as varchar(max))
    			ELSE cast (TreCTE.N0 as varchar(max))
    			END ,
    		   
    		   N1 = CASE 
    			WHEN Tre.NodeLevel = 1 AND TreCTE.N1 = '' THEN cast (Tre.NodeName as varchar(max))
    			ELSE cast (TreCTE.N1 as varchar(max))
    			END ,
    
    		   N2 = CASE 
    			WHEN Tre.NodeLevel = 2 AND TreCTE.N2 = '' THEN cast (Tre.NodeName as varchar(max))
    			ELSE cast (TreCTE.N2 as varchar(max))
    			END ,
    		   
    		   N3 = CASE 
    			WHEN Tre.NodeLevel = 3 AND TreCTE.N3 = '' THEN cast (Tre.NodeName as varchar(max))
    			ELSE cast (TreCTE.N3 as varchar(max))
    			END ,
    
    			coalesce(cast(TreCTE.N4 as varchar(max)), cast(Tre.NodeName as varchar(max)) ) as N4,
    
    		   cast(Tre.NodeName+ '-' + TreCTE.NodePath as varchar(max)),
    		   TreCTE.NodePath
    		   
    	from TreeCTE as TreCTE
    	join dbo.Tree_Alarms as Tre
    	  on Tre.NodeID  = TreCTE.ParentID )
    			select --SUBSTRING(NodePath, 1, (LEN(NodePath)-1)) as AlarmPath, 
    			AlarmPath,
    			NodeName,
    			N0 as [SITE], N1 as [AREA], N2 as [BUILDING], N3 as [SYSTEM], N4 as [TECHADDR], NodeLevel
    	from TreeCTE
    I'm sure this could have been done more efficiently.

    Henry

    • Marked as answer by Henry64 Monday, October 21, 2013 9:27 AM
    Monday, October 21, 2013 9:26 AM