The link you provide already has the sample code to populate the helper
table. Maybe I missed your question.
This is your link:
http://www.bimonkey.com/2009/09/handling-recursive-hierarchies-in-sql-server/
This is the link to the SQL example:
http://www.bimonkey.com/uploads/sql/RecursiveHelper.sql
I pasted the code here:
/*************************************************************************
Handling Recursive hierarchies using the Kimball Helper Table Method
A code sample from www.bimonkey.com
Written by James Beresford for www.bimonkey.com on 18/09/2009
Please contact via email at the admin account of the bimonkey.com domain
Re-use for educational purposes is permitted but if used in
any environment no liability is accepted unless by
prior commercial agreement.
************************************************************************/
/*************************************************************************
Index
Step 1: Create tables
Step 2: Populate Source tables
Step 3: Populate Helper table
Step 4: Demonstrate use of Helper Table
************************************************************************/
/*************************************************************************
Step 1: Create tables
dbo.Hierarchy - contains the recursive Hierarchy
dbo.Values - contains the values for the Hierarchy
dbo.RecursiveHelper - contains the LR Values
************************************************************************/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Hierarchy](
[ParentLevel] [nchar](10) NOT NULL,
[ChildLevel] [nchar](10) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Values](
[Level] [nchar](10) NOT NULL,
[Value] [tinyint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[RecursiveHelper](
[ParentKey] [nchar](10) NULL,
[ChildKey] [nchar](10) NULL,
[DepthFromParent] [smallint] NULL,
[LowestFlag] [nchar](1) NULL,
[TopmostFlag] [nchar](1) NULL
) ON [PRIMARY]
GO
/*************************************************************************
Step 2: Populate source tables
dbo.Hierarchy - contains the recursive Hierarchy
dbo.Values - contains the values for the Hierarchy
************************************************************************/
INSERT INTO [dbo].[Hierarchy]
([ParentLevel]
,[ChildLevel])
VALUES
('A1','A2'),
('A1','A3'),
('A2','A4'),
('A2','A5'),
('A3','A6'),
('B1','B2'),
('B1','B3'),
('B2','B4'),
('B3','B5'),
('B3','B6'),
('B6','B7'),
('B6','B8')
GO
INSERT INTO [dbo].[Values]
([Level]
,[Value])
VALUES
('A1','43'),
('A2','32'),
('A3','22'),
('A4','10'),
('A5','33'),
('A6','20'),
('B1','35'),
('B2','21'),
('B3','23'),
('B4','48'),
('B5','40'),
('B6','14'),
('B7','17'),
('B8','35')
GO
/*************************************************************************
Step 3: Populate Helper table
dbo.RecursiveHelper - contains the recursive Hierarchy
************************************************************************/
DECLARE @ParentNode nchar(10)
DECLARE @ChildNode nchar(10)
DECLARE @PreviousLevelNodes TABLE (Node nchar(10))
DECLARE @CurrentLevelNodes TABLE (Node nchar(10))
DECLARE @LevelDepth int
-- Cursor for each Parent Node
DECLARE Nodes CURSOR FOR
SELECT DISTINCT ParentLevel FROM [dbo].[Hierarchy]
OPEN Nodes
FETCH NEXT FROM Nodes INTO @ParentNode
WHILE @@FETCH_STATUS = 0
-- Loop through each Parent Node
BEGIN
-- Create self reference entry
INSERT INTO [dbo].[RecursiveHelper] VALUES
(@ParentNode,@ParentNode,0,'N','N')
-- Initialise Current Level Node Set
INSERT INTO @CurrentLevelNodes
SELECT ChildLevel FROM dbo.Hierarchy
WHERE ParentLevel = @ParentNode
-- Loop through each level below parent in main loop
WHILE (SELECT COUNT(*) FROM @CurrentLevelNodes) > 0
BEGIN
-- Cursor for each child of Current parent node
DECLARE ChildNodes CURSOR FOR
SELECT *
FROM @CurrentLevelNodes
OPEN Childnodes
FETCH NEXT FROM Childnodes INTO @ChildNode
WHILE @@FETCH_STATUS = 0
-- Create entry in RecursiveHelper table for each child of Current Parent
BEGIN
INSERT INTO [dbo].[RecursiveHelper] VALUES
(@ParentNode,@ChildNode,0,'N','N')
FETCH NEXT FROM Childnodes INTO @ChildNode
END
-- Close and Deallocate Child Node cursor
CLOSE childnodes
DEALLOCATE childnodes
-- park values
DELETE FROM @PreviousLevelNodes
INSERT INTO @PreviousLevelNodes
SELECT * FROM @CurrentLevelNodes
DELETE FROM @CurrentLevelNodes
-- Get next values for currentlevelnodes
INSERT INTO @CurrentLevelNodes
SELECT ChildLevel
FROM [dbo].[Hierarchy]
WHERE ParentLevel IN (SELECT * FROM @PreviousLevelNodes)
end
FETCH NEXT FROM Nodes INTO @ParentNode
END
-- Close and Deallocate Parent node cursor
CLOSE Nodes
DEALLOCATE Nodes
-- Update Topmost Flag by identifying all parent nodes with no parents
UPDATE dbo.RecursiveHelper
SET [TopmostFlag] = 'Y'
WHERE ParentKey IN ( SELECT ParentLevel
FROM dbo.Hierarchy
WHERE ParentLevel NOT IN ( SELECT DISTINCT ChildLevel
FROM dbo.Hierarchy ) )
-- Update Lowest Flag by identifying all child nodes with no children
UPDATE dbo.RecursiveHelper
SET [LowestFlag] = 'Y'
WHERE ChildKey IN ( SELECT ChildLevel
FROM dbo.Hierarchy
WHERE ChildLevel NOT IN ( SELECT DISTINCT ParentLevel
FROM dbo.Hierarchy ) )
-- Create Lowest Level records
INSERT INTO dbo.RecursiveHelper
SELECT Distinct ChildKey, ChildKey, DepthFromParent, LowestFlag, 'N' AS
TopmostFlag
FROM dbo.RecursiveHelper
WHERE LowestFlag = 'Y'
-- Update Depth From Parent indicator for all records where not yet set
DECLARE Parents CURSOR FOR
SELECT DISTINCT ParentKey
FROM dbo.RecursiveHelper
WHERE ParentKey = ChildKey and TopmostFlag = 'Y'
OPEN Parents
FETCH NEXT FROM Parents INTO @ParentNode
WHILE @@FETCH_STATUS = 0
BEGIN
SET @LevelDepth = 1
UPDATE dbo.RecursiveHelper
SET DepthFromParent = @LevelDepth
WHERE ParentKey = @ParentNode
AND ParentKey <> ChildKey
AND TopmostFlag = 'Y'
INSERT INTO @CurrentLevelNodes
SELECT @ParentNode
WHILE (SELECT COUNT(*) FROM @CurrentLevelNodes) > 0
BEGIN
-- Move Current Parent Nodes to Previous Parent Nodes
DELETE FROM @PreviousLevelNodes
INSERT INTO @PreviousLevelNodes
SELECT * FROM @CurrentLevelNodes
-- Clear Parent Nodes table
DELETE FROM @CurrentLevelNodes
-- Get parents of next level down
INSERT INTO @CurrentLevelNodes
SELECT DISTINCT ChildLevel
FROM dbo.Hierarchy
WHERE ParentLevel IN ( SELECT * FROM @PreviousLevelNodes )
-- Update self referencing nodes
UPDATE dbo.RecursiveHelper
SET DepthFromParent = @LevelDepth
WHERE ParentKey IN ( SELECT * FROM @CurrentLevelNodes )
AND ParentKey = ChildKey
SET @LevelDepth = @LevelDepth + 1
END
FETCH NEXT FROM Parents INTO @ParentNode
END
CLOSE Parents
DEALLOCATE Parents
UPDATE dbo.RecursiveHelper
SET DepthFromParent = ( SELECT DepthFromParent
FROM dbo.RecursiveHelper h
WHERE dbo.RecursiveHelper.ChildKey = h.ChildKey
AND h.ParentKey = h.ChildKey )
WHERE DepthFromParent = 0 AND TopmostFlag <> 'Y'
/*************************************************************************
Step 4: Demonstrate use of Helper Table
************************************************************************/
DECLARE @Node nchar(10)
SET @Node = 'A2'
SELECT SUM([Value])
FROM dbo.RecursiveHelper r
LEFT JOIN dbo.[Values] v
ON r.ChildKey = v.[Level]
where r.ParentKey = @Node
--
Shin
MS SQL Server
Post by iggster_194Hi,
has anyone out there written SQLServer 2005 code to populate a Kimball
Recursive Helper table as per
http://www.bimonkey.com/2009/09/handling-recursive-hierarchies-in-sql-server ?
These is an implementation using Oracle via
http://www.rkimball.com/html/designtipsPDF/DesignTips2001/KimballDT17Populating.pdf but I can't find a Transact-SQL version.
Thanks