Flatten a Hierarchy in SQL Server 2005
Hierarchies are common in many applications today. Some classic examples are the reporting chain in your company. For example you are an employee and you report to another employee and that employee. A common way to represent this is utilizing an "Adjacency List Model". A good explanation of this can be found in Joe Celko’s book and on this website (
http://www.sqlsummit.com/AdjacencyList.htm). The basic idea is that a boss column exists on the employee table a unary relationship. So utilizing the new common table expression (
http://msdn2.microsoft.com/en-us/library/ms175972.aspx) in sql server 2005 it is easy to build a hierarchical query. Once the hierarcial query is built on needs to add a column that build the entire tree up. In the example in the sql server books online this is done in the sort field. The only issue we have is that it is difficult to break up the tree into its individual parts since the pipe delimiters are at different places. The easy fix to this is to pad the field so that every name is the same length. Then I can use a simple substring to break the names back apart into individual columns. Poof, a hierarchy is now horizontal in columns! YEA!
USE AdventureWorks;
GO
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)
AS (SELECT CONVERT(varchar(255), c.FirstName + ' ' + c.LastName),
e.Title,
e.EmployeeID,
1,
CONVERT(varchar(max), CONVERT(char(100), c.LastName + ', ' + c.FirstName) + '|')
FROM HumanResources.Employee AS e
JOIN Person.Contact AS c ON e.ContactID = c.ContactID
WHERE e.ManagerID IS NULL
UNION ALL
SELECT CONVERT(varchar(255), c.FirstName + ' ' + c.LastName),
e.Title,
e.EmployeeID,
EmployeeLevel + 1,
CONVERT(varchar(max), Sort + CONVERT(char(100), c.LastName + ', ' + c.FirstName) + '|')
FROM HumanResources.Employee as e
JOIN Person.Contact AS c ON e.ContactID = c.ContactID
JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
SELECT
EmployeeID,
Name,
Title,
EmployeeLevel,
RTRIM(SUBSTRING(sort, 1, 100)) AS lvl1,
RTRIM(SUBSTRING(sort, 102, 100)) AS lvl2,
RTRIM(SUBSTRING(sort, 203, 100)) AS lvl3,
RTRIM(SUBSTRING(sort, 304, 100)) AS lvl4,
RTRIM(SUBSTRING(sort, 506, 100)) AS lvl5,
RTRIM(SUBSTRING(sort, 607, 100)) AS lvl6,
RTRIM(SUBSTRING(sort, 708, 100)) AS lvl7,
RTRIM(SUBSTRING(sort, 809, 100)) AS lvl8,
RTRIM(SUBSTRING(sort, 910, 100)) AS lvl9,
RTRIM(SUBSTRING(sort, 1011, 100)) AS lvl10
FROM DirectReports
ORDER BY Sort;
GO