Friday, August 7, 2009

Query Hierarchical data Using CTE in T-SQL 2005

this script uses the new CTE feature of SQL Server 2005 to display the hierarchical data all at once. I have added a check to prevent infinite loop in case there is a loop in the data (actually, there is one in the example). But this script has some limitations:

1) The maximum number of childern for a parent is 2^33. But you can enlarge this number by expanding the slots in s column (currently, it's 10 characters wide for each level).

2) The maximum number of levels is 100. This is the limitation of T-SQL.


CREATE TABLE Hierarchy(
Parent VARCHAR(20) NOT NULL,
Child VARCHAR(20),
CONSTRAINT UIX_ParentChild
UNIQUE NONCLUSTERED (Parent,Child)
)
GO
CREATE CLUSTERED INDEX CIX_Parent ON Hierarchy(Parent)
GO
INSERT Hierarchy VALUES('World','Europe')
INSERT Hierarchy VALUES('World','North America')
INSERT Hierarchy VALUES('Europe','France')
INSERT Hierarchy VALUES('France','Paris')
INSERT Hierarchy VALUES('North America','United States')
INSERT Hierarchy VALUES('North America','Canada')
INSERT Hierarchy VALUES('United States','New York')
INSERT Hierarchy VALUES('United States','Washington')
INSERT Hierarchy VALUES('New York','New York City')
INSERT Hierarchy VALUES('Washington','Redmond')
--The following row will generate a loop regarding the 'World'
INSERT Hierarchy VALUES('Redmond', 'World')
GO

Declare @Root nvarchar(100);
Set @Root ='World';
With t as (
Select parent = convert(varchar(20),'--'),
Child = convert(varchar(20),@Root),
L = 0,
S = Convert(varchar(max),'')
union all
select h.*, t.L+1, t.S+ convert(varchar(max),right('0000000000'+convert(varchar, row_number() over (order by h.Parent )),10))
from hierarchy h
join t on h.parent = t.child and h.Child <> @Root
)
Select space(L)+Child, * from t order by s;

No comments: