Menu
I have the following table in a SQL Server 2008 database:
Id Name ParentFolder
-- ---- ------------
1 Europe NULL
2 Asia NULL
3 Germany 1
4 UK 1
5 China 2
6 India 2
7 Scotland 4
ParentFolder is a FK to Id in the same table. I would like to create a view that results in something like this:
Id Name FullName
-- ---- --------
1 Europe Europe
2 Asia Asia
3 Germany Europe/Germany
4 UK Europe/UK
5 China Asia/China
6 India Asia/India
7 Scotland Europe/UK/Scotland
As you can see, I need to build the FullName values by recursively using the ParentFolder relationship an arbitrary number of times until a NULL is found.
Edit. Each row in the table "knows" what other row is its parent, but does not know its absolute position in the hierarchy. For this reason, a lineage system where each row stores its absolute location in the hierarchy tree would not be appropriate.
I am aware of the hierarchyid feature of SQL Server 2008 but, as far as I know, it only works with a fixed number of recursion levels. In my case, however, you never know how many levels you will find, and they may change from row to row.
I have also seen similar questions to this posted here. However, I think that nobody asked about building "paths" for each row in a table. Sorry if I missed it.
Many thanks.
452
Recursion is achieved by WITH statement, in SQL jargon called Common Table Expression (CTE). It allows to name the result and reference it within other queries sometime later. Naming the result and referencing it within other queries.
Recursion occurs because of the query referencing the CTE itself based on the Employee in the Managers CTE as input. The join then returns the employees who have their managers as the previous record returned by the recursive query. The recursive query is repeated until it returns an empty result set.
A Recursive CTE is a CTE that references itself. The CTE repeatedly executes, returns subsets of data, until it returns the complete result set. Syntax. WITH cte_name AS ( cte_query_definition (or) initial query -- Anchor member UNION ALL recursive_query with condition -- Recursive member ) SELECT * FROM cte_name.
A recursive subquery factoring clause must contain two query blocks combined by a UNION ALL set operator. The first block is known as the anchor member, which can not reference the query name. It can be made up of one or more query blocks combined by the UNION ALL , UNION , INTERSECT or MINUS set operators.
Try this one:
DECLARE @tbl TABLE (
Id INT
,[Name] VARCHAR(20)
,ParentId INT
)
INSERT INTO @tbl( Id, Name, ParentId )
VALUES
(1, 'Europe', NULL)
,(2, 'Asia', NULL)
,(3, 'Germany', 1)
,(4, 'UK', 1)
,(5, 'China', 2)
,(6, 'India', 2)
,(7, 'Scotland', 4)
,(8, 'Edinburgh', 7)
,(9, 'Leith', 8)
;
WITH abcd
AS (
-- anchor
SELECT id, [Name], ParentID,
CAST(([Name]) AS VARCHAR(1000)) AS "Path"
FROM @tbl
WHERE ParentId IS NULL
UNION ALL
--recursive member
SELECT t.id, t.[Name], t.ParentID,
CAST((a.path + '/' + t.Name) AS VARCHAR(1000)) AS "Path"
FROM @tbl AS t
JOIN abcd AS a
ON t.ParentId = a.id
)
SELECT * FROM abcd
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
