(Yet another boring org chart example – except this one has multiple roots)
One of my apps has a hierarchial org chart in its database. We don’t use the hierarchyid data type for this table. Instead, each row in the table just has a unique org ID and the org ID of the parent org which should exist in the same table. The ParentOrgID field is NULL in the rows for the root orgs. Notice I said root orgs – plural. We have an odd situation with this database where there’s actually two different companies’ trees stored in this single table. I recently had a requirement to pull out all the orgs for both companies and say what the root was, in addition to presenting how deep in the hierarchy it lived. Here’s how I did it.
--Example source table
DECLARE @Orgs AS TABLE (
OrgID INT PRIMARY KEY,
ParentOrgID INT NULL,
OrgName VARCHAR(20) NOT NULL
);
--Set up some sample data
INSERT INTO @Orgs (OrgID, ParentOrgID, OrgName) VALUES
(1,NULL,'Company A (Root)'),
(2,1,'Marketing'),
(3,1,'Finance'),
(4,3,'Accounting'),
(5,2,'Sales'),
(6,1,'Research'),
(7,NULL,'Company B (Root)'),
(8,7,'Production'),
(9,7,'Development'),
(10,7,'Processing'),
(11,10,'Shipping'),
(12,3,'IT'),
(13,8,'Industrial Relations'),
(14,2,'Branding'),
(15,11,'Intl Shipping'),
(16,12,'DBAs'),
(17,12,'Server Ops'),
(18,12,'Desktop Support');
--Declare some constants pointed at my root orgs (could also use a config table)
DECLARE @ARoot INT;
DECLARE @BRoot INT;
SELECT @ARoot = OrgID FROM @Orgs WHERE OrgName = 'Company A (Root)';
SELECT @BRoot = OrgID FROM @Orgs WHERE OrgName = 'Company B (Root)';
WITH AllOrgs AS --setting up the CTE
(SELECT OrgID,
CASE WHEN OrgID = @ARoot then 'A' --This case block is my way of dealing with
WHEN OrgID = @BRoot then 'B' --aliasing the company names as a code
ELSE '' END as [RootCompany],
1 as [OrgLevel], --C.S. students would put a 0 here...
OrgName, ParentOrgID FROM @Orgs
Where OrgID = @ARoot OR OrgID = @BRoot --This is the query for the first row
UNION ALL
SELECT c.OrgID, p.RootCompany, p.OrgLevel + 1, c.OrgName, c.ParentOrgID
from AllOrgs p
INNER JOIN @Orgs c
on c.ParentOrgID = p.OrgID --query for remaining rows
)
SELECT OrgID, RootCompany, OrgLevel,Orgname, ParentOrgID
FROM AllOrgs Order by RootCompany, OrgLevel, OrgName; --returns the CTE data
Inside the CTE are two SELECT statements married by a UNION ALL. The first one is basically for the first line of data to return (or first lines in this case, since there are two possible roots).
The second query returns the data for each successive row, recursively. SQL Server will automatically quit recursion when a row is null so you don’t have to worry about closing the loop yourself somehow as long as you do the join correctly and there are no circular references. In the second query, “p” aliases the CTE itself which allows us to read the parent row’s data and do some basic operations such as adding one to its OrgLevel value. Note that as with all UNION statements in SQL Server, you only have to alias the column names in the top query – column names in later queries are ignored.
Finally, I select the data I want with my desired sorting. CTEs self-destruct after use (note the semicolon after the select, but not after the closing parenthesis after the CTE setup), so if you need to do multiple operations on the results, insert them into a temp table first.
