I keep getting the need to quickly Naturalize hierachies for analysis. So here is a sample script that accomplishes this:
set nocount on
declare @Temp1 table
(
RecordID int Primary key NOT NULL ,
ParentRecordID int,
Description nvarchar(1000)
)
/* Start loading of test data */
insert into @Temp1 values(1,null,'CEO')
insert into @Temp1 values(2,1,'VP Marketing')
insert into @Temp1 values(3,1,'VP Operations')
insert into @Temp1 values(4,2,'Marketing Director - Direct Mail')
insert into @Temp1 values(5,2,'Marketing Director - TV')
insert into @Temp1 values(6,1,'VP Research')
insert into @Temp1 values(7,4,'Human Resources Director')
insert into @Temp1 values(8,4,'Program Manager')
insert into @Temp1 values(9,6,'Research Analyst')
set nocount off;
with RecursionCTE (RecordID,ParentRecordID, Description, TOC, L1ID, Level1, L2ID, Level2, L3ID, Level3, L4ID, Level4, LevelCount)
as
(
select RecordID,ParentRecordID, Description, convert(varchar(1000),Description) TOC, RecordID as L1ID, Description as Level1, CONVERT(INT, NULL) as L2ID, Convert(nvarchar(1000), '') as Level2, CONVERT(INT, NULL) as L3ID, Convert(nvarchar(1000), '') as Level3, CONVERT(INT, NULL) as L4ID, Convert(nvarchar(1000), '') as Level4, Convert(int, 1) as LevelCount
from @Temp1
where ParentRecordID is null
union all
select R1.RecordID,
R1.ParentRecordID,
R1.Description,
case when DataLength(R2.TOC) > 0
then convert(varchar(1000),R2.TOC + '->'
+ cast(R1.Description as varchar(100)))
else convert(varchar(1000),
cast(R1.Description as varchar(100)))
end as TOC,
L1ID = R2.L1ID,
Level1 = R2.Level1,
L2ID = convert(INT, CASE WHEN R2.LevelCount = 1 THEN R1.RecordID ELSE R2.L2ID END),
Level2 = convert(nvarchar(1000), CASE WHEN R2.LevelCount = 1 THEN R1.Description ELSE R2.LEVEL2 END),
L3ID = convert(INT, CASE WHEN R2.LevelCount = 2 THEN R1.RecordID ELSE R2.L3ID END),
Level3 = convert(nvarchar(1000), CASE WHEN R2.LevelCount = 2 THEN R1.Description ELSE R2.LEVEL3 END),
L4ID = convert(INT, CASE WHEN R2.LevelCount = 3 THEN R1.RecordID ELSE R2.L4ID END),
Level4 = convert(nvarchar(1000), CASE WHEN R2.LevelCount = 3 THEN R1.Description ELSE R2.LEVEL4 END),
LevelCount = R2.LevelCount + 1
from @Temp1 as R1
join RecursionCTE as R2 on R1.ParentRecordID = R2.RecordID
)
select * from RecursionCTE
