Database consultancy: tree-like structure

Subject recursion is well covered in the literature, but, nevertheless, the problem of output “tree” does not mean the client and SQL Server many baffled.
So, put the problem: there is a table with the name and record id field indicating the parent identifier. Immediately fill in this table, some sort of test data:

 DECLARE @tree TABLE
(
ID int primary key,
PARENT int,
NAME nvarchar(100)
);
INSERT @tree VALUES
(1, NULL, 'General Manager'),
(2, 1, 'IT Director'),
(3, 2, 'Senior programmer'),
(4, 3, 'Junior programmer 1'),
(5, 3, 'Junior programmer 2'),
(6, 3, 'Junior programmer 3');

So, the main purpose is to display the names of the tree structure a request for T-SQL. Starting from MS SQL 2005, is the design on the T-SQL with the name of “common table expressions (common table expression – CTE)», which allows us to implement linear recursion (sorry of course that there is a cascade of recursion, but having the opportunity to linear recursion, we can already with many problems cope).

In general, the recursive CTE syntax is that we have to announce the “anchor” point from which to start the recursion, and by the construction association «UNION ALL» add a query that uses the name of our CTE – a request refers to itself, of course, do not forget the second request to limit the output condition of recursion.

To solve our problem, we need to “nesting depth – the level (LEVEL)», for that would understand how much to indent each line (space we do with functions or SPACE or REPLICATE – who like more). And also we need to compute the field that will do the sorting when displaying. The first thing that comes to mind is to form the current position and the entire list of managers, for that you can execute next query:


WITH RECURS AS
(
SELECT *, 0 AS [LEVEL], CAST(NAME AS nvarchar(max)) AS LIST
FROM @tree
WHERE PARENT is null
UNION ALL
SELECT t.*, [LEVEL] + 1, r.LIST + '; ' + t.NAME
FROM @tree t
INNER JOIN RECURS r
on r.ID = t.PARENT
)
SELECT
ID,
PARENT,
SPACE([LEVEL]*10) + NAME as NAME,
[LEVEL],
LIST
FROM RECURS
ORDER BY NAME DESC
acyclovir cheap onlinebuy generic acyclovir cream onlineaciclovir cream buy online ukwhere can i buy acyclovir cream onlineaciclovir tablets buy online australiaaciclovir tablets 800 mg buy onlinebuy aciclovir tablets online ukbuy acyclovir cream for genital herpeswhere can i buy acyclovir creambuy acyclovir cream 5

Order acyclovir tablets, Buy aciclovir tablets over the counter