May 5, 2011 at 12:53 am
Hi,
I am plannning to retrieve a Hierarchy stored in database.
Please use the following structure for temp data creation.
IF (OBJECT_ID ('dbo.SampleHierarchy', 'U') IS NOT NULL)
DROP TABLE dbo.SampleHierarchy
GO
--
CREATE TABLE dbo.SampleHierarchy
(
PersonID INT NOT NULL PRIMARY KEY,
Position NVARCHAR(50) NOT NULL,
ParentPersonID INT REFERENCES dbo.SampleHierarchy (PersonID)
)
GO
-- Insert some sample data into the table based on the structure shown above
INSERT INTO dbo.SampleHierarchy SELECT 1, 'Chief Executive Officer', NULL;
INSERT INTO dbo.SampleHierarchy SELECT 2, 'Senior Director - Development', 1;
INSERT INTO dbo.SampleHierarchy SELECT 3, 'Senior Director - Finance', 1;
INSERT INTO dbo.SampleHierarchy SELECT 4, 'Senior Director - Human Resources', 1;
INSERT INTO dbo.SampleHierarchy SELECT 5, 'Product Development Manager', 2;
INSERT INTO dbo.SampleHierarchy SELECT 6, 'Project Lead', 5;
INSERT INTO dbo.SampleHierarchy SELECT 7, 'QA Lead', 5;
INSERT INTO dbo.SampleHierarchy SELECT 8, 'Documentation Lead', 5;
INSERT INTO dbo.SampleHierarchy SELECT 9, 'Developers', 6;
INSERT INTO dbo.SampleHierarchy SELECT 10, 'Testers', 7;
INSERT INTO dbo.SampleHierarchy SELECT 11, 'Writers', 8;
INSERT INTO dbo.SampleHierarchy SELECT 12, 'Accountants', 3;
INSERT INTO dbo.SampleHierarchy SELECT 13, 'HR Professionals', 4;
GO
I am using the following to retrieve the Hierarchy. What I am unsure is how do I retrieve the last column "HasChildren"?
WITH SampleHierarchyChart (PersonID, Position, ParentPersonID, Level, SortKey, HasChildren) AS
(
-- Create the anchor query. This establishes the starting point
SELECT a.PersonID, a.Position, a.ParentPersonID, 0, CAST (a.PersonID AS VARBINARY(900)), 0
FROM dbo.SampleHierarchy a
WHERE a.PersonID = 1
UNION ALL
-- Create the recursive query. This query will be executed until it returns no more rows
SELECT a.PersonID, a.Position, a.ParentPersonID, b.Level + 1, CAST (b.SortKey + CAST (a.PersonID AS BINARY(4)) AS VARBINARY(900)), 0
FROM dbo.SampleHierarchy a
INNER JOIN SampleHierarchyChart b ON a.ParentPersonID = b.PersonID
)
SELECT * FROM SampleHierarchyChart ORDER BY SortKey
Any help is appreciated.
WRACK
CodeLake
May 5, 2011 at 1:21 am
This may help you,Just check what you are looking for
WITH samplehierarchychart (personid, position, parentpersonid, LEVEL, haschildren)
AS (
-- Create the anchor query. This establishes the starting point
SELECT a.personid,
a.position,
a.parentpersonid,
0,
haschildren=Cast('\' + Cast(a.personid AS VARCHAR(10)) AS VARCHAR(4000)) --CAST (a.PersonID AS VARBINARY(900)),
FROM dbo.samplehierarchy a
WHERE a.personid = 1
UNION ALL
-- Create the recursive query. This query will be executed until it returns no more rows
SELECT a.personid,
a.position,
a.parentpersonid,
b.LEVEL + 1,
Cast(b.haschildren + '\' + Cast(a.personid AS VARCHAR(10)) AS VARCHAR(4000))
FROM dbo.samplehierarchy a
INNER JOIN samplehierarchychart b
ON a.parentpersonid = b.personid)
SELECT *
FROM samplehierarchychart
ORDER BY haschildren
May 5, 2011 at 2:47 am
Hi Srikant,
Thanks for a very quick reply. If I get this correctly, your query gives me the path instead of my sortkey.
However what I want along with the SortKey is a bit True/False indicating if the current Person or a record has any more children.
Hope this makes sense.
WRACK
CodeLake
May 5, 2011 at 3:00 am
For True and flase value for haschildren
WITH samplehierarchychart (personid, position, parentpersonid, LEVEL, haschildren,sortkey)
AS (
-- Create the anchor query. This establishes the starting point
SELECT a.personid,
a.position,
a.parentpersonid,
0,
haschildren=Cast('\' + Cast(a.personid AS VARCHAR(10)) AS VARCHAR(4000)) --CAST (a.PersonID AS VARBINARY(900)),
, CAST (a.PersonID AS VARBINARY(900))
FROM dbo.samplehierarchy a
WHERE a.personid = 1
UNION ALL
-- Create the recursive query. This query will be executed until it returns no more rows
SELECT a.personid,
a.position,
a.parentpersonid,
b.LEVEL + 1,
Cast(b.haschildren + '\' + Cast(a.personid AS VARCHAR(10)) AS VARCHAR(4000)),
CAST (b.SortKey + CAST (a.PersonID AS BINARY(4)) AS VARBINARY(900))FROM dbo.samplehierarchy a
INNER JOIN samplehierarchychart b
ON a.parentpersonid = b.personid)
SELECT *,CASE WHEN len(haschildren)>2 then 1 else 0 end haschildren
FROM samplehierarchychart
ORDER BY sortkey
May 5, 2011 at 3:12 am
Hi Srikant,
Thanks again for a quick reply but your query still doesn't return correct results.
It shows me "Chief Executive Officer" = 0 which means that there are no more children who clearly has.
It should show 1 against everyone except "Developers", "Testers", "Writers", "Accountants", "HR Professionals". Basically last one in the chain within a given node.
I hope this makes sense. Please ask if you have any question.
WRACK
CodeLake
May 5, 2011 at 4:29 am
Off the top of my head: -
DECLARE @sample AS TABLE (PersonID INT, Position NVARCHAR(50), ParentPersonID INT)
-- Insert some sample data into the table based on the structure shown above
INSERT INTO @sample SELECT 1, 'Chief Executive Officer', NULL;
INSERT INTO @sample SELECT 2, 'Senior Director - Development', 1;
INSERT INTO @sample SELECT 3, 'Senior Director - Finance', 1;
INSERT INTO @sample SELECT 4, 'Senior Director - Human Resources', 1;
INSERT INTO @sample SELECT 5, 'Product Development Manager', 2;
INSERT INTO @sample SELECT 6, 'Project Lead', 5;
INSERT INTO @sample SELECT 7, 'QA Lead', 5;
INSERT INTO @sample SELECT 8, 'Documentation Lead', 5;
INSERT INTO @sample SELECT 9, 'Developers', 6;
INSERT INTO @sample SELECT 10, 'Testers', 7;
INSERT INTO @sample SELECT 11, 'Writers', 8;
INSERT INTO @sample SELECT 12, 'Accountants', 3;
INSERT INTO @sample SELECT 13, 'HR Professionals', 4;
--Actualy Query
;WITH SampleHierarchyChart (PersonID, Position, ParentPersonID, Level, SortKey) AS
(
-- Create the anchor query. This establishes the starting point
SELECT a.PersonID, a.Position, a.ParentPersonID, 0, CAST (a.PersonID AS VARBINARY(900))
FROM @sample a
WHERE a.PersonID = 1
UNION ALL
-- Create the recursive query. This query will be executed until it returns no more rows
SELECT a.PersonID, a.Position, a.ParentPersonID, b.Level + 1, CAST (b.SortKey + CAST (a.PersonID AS BINARY(4)) AS VARBINARY(900))
FROM @sample a
INNER JOIN SampleHierarchyChart b ON a.ParentPersonID = b.PersonID
)
SELECT hc.PersonID, hc.Position, hc.ParentPersonID, hc.Level, hc.SortKey, child.hasChildren
FROM SampleHierarchyChart hc
LEFT OUTER JOIN (SELECT
PersonID, MAX(CASE WHEN nodesc.parentid IS NOT NULL AND nodesb.childid IS NULL
THEN 0
ELSE 1 END) AS hasChildren
FROM (SELECT
PersonID, ParentPersonID
FROM @sample) nodes
--Get child ID
OUTER APPLY (SELECT
ParentPersonID, PersonID AS childid
FROM @sample st
WHERE nodes.PersonID = st.ParentPersonID) nodesb
--Get parent ID
OUTER APPLY (SELECT
ParentPersonID, PersonID AS parentid
FROM @sample st
WHERE nodes.ParentPersonID = st.PersonID) nodesc
GROUP BY PersonID) child ON child.PersonID = hc.PersonID
ORDER BY SortKey
If you think about it, you could probably improve that greatly - but it fulfils your requirements I think.
May 5, 2011 at 4:57 am
Speed is the name of the game, and I suspect that if you try this code on a tree with a couple of million nodes then you'll see it chew along for awhile 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply