In this post we create a parent child hierarchy (n level) from a delimited string like [Parent_Child_LeafChild].
What we are looking for is taking a n length string like "Stage1_Stage2_Stage3" and moving it into a table to build this:
What we are looking for is taking a n length string like "Stage1_Stage2_Stage3" and moving it into a table to build this:
Stage1
--Stage2
----Stage3
DECLARE @separator_position INT
-- This is used to locate each separator character
DECLARE @array_value VARCHAR(1000)-- this holds each array value as it is returned
DECLARE @separator char(1) --Used in WHERE clause
declare @StageLevel varchar(255) = 'Stage1_Stage2_Stage3'
declare @holder varchar(255) = @stagelevel
SET @separator = '_' --Separator A.K.A.
Delimiter
SET @StageLevel = @StageLevel + @separator --append ',' at
the end
-- select
PATINDEX('%[' + @separator + ']%', @StageLevel)
Declare @Level int = 0
Declare @ParentId int
WHILE PATINDEX('%[' + @separator + ']%', @StageLevel) <> 0
BEGIN -- patindex
matches the a pattern against a string
SELECT @separator_position
= PATINDEX('%[' + @separator + ']%',@StageLevel)
SELECT @array_value
= LEFT(@StageLevel, @separator_position -
1)
set @level
= @level +1
--select @array_value, @level
IF @level =1 --This is parent node
Begin
insert into Hierarchy (StageLevelName, StageLevel)
values (@array_value, @Level)
END
ELSE
BEGIN
insert into Hierarchy (StageLevelName, StageLevel, ParentID)
values (@array_value, @Level, @ParentId)
END
set @ParentId = @@IDENTITY
select @ParentId
--Moving to end of array
SELECT @StageLevel
= STUFF( @StageLevel, 1, @separator_position, '')
END
The end result will be a table with each row as a recursive hierarchy. Plus it has level information for depth.
