In SQL Server 2005 was an innovation in the form of CTE (common table expressions), which enables the hierarchical recursive queries to the database. While designing the web site
автокредит на автомобиль I have a challenge: to display the hierarchical structure of pages of the site. The query also have to sort sort the data on a field "pageorder" from the pages with the same level of hierarchy.
The structure of the table looks like this:
CREATE TABLE [dbo].[pages](
[id] [int] NULL,
[pid] [int] NULL,
[title] [nvarchar](max) NULL,
[pageorder] [int] NULL
) ON [PRIMARY]
The field "pid" points to id of the parent record. In addition, there have to exist a root(entry) record, whose pid = NULL.
Below is a sample request that selects and sorts the hierarchical data:
WITH CTE(pid, id, title, level, pageorder, pathstr) AS
(
SELECT pid, id, title, 0, 0, CAST ('/' AS VARCHAR(MAX))
FROM pages
WHERE pid IS NULL
UNION ALL
SELECT e.pid, e.id, e.title, level + 1, e.pageorder, d.pathstr + CAST(e.pageorder as VARCHAR) + '/'
FROM pages e
INNER JOIN CTE d
ON e.pid = d.id
)
SELECT pid, id, replicate('... ', level) + title as title, level, pageorder, pathstr
FROM CTE
ORDER BY pathstr
Here's what we get after execution the query:
pid id level pageorder pathstr title
----------- ----------- ----------- ----------- ----------- ---------------------------------
NULL 1 0 0 / Main
1 4 1 3 /3/ ... Products
4 5 2 3 /3/3/ ... ... cars
4 8 2 6 /3/6/ ... ... motorcycles
1 2 1 6 /6/ ... Services
2 6 2 3 /6/3/ ... ... Lease
2 3 2 6 /6/6/ ... ... Loans
1 7 1 9 /9/ ... about website
(8 row(s) affected)
