Tuesday, May 13, 2008

Recursive SQL Query

Some time ago I was done a ton do SQL 2005 development and I tons of requirements to do recursive queries. Well I had it come up again where I wanted to return back a tree of data. Not to say I could to the recursion on in some .Net code however it is a little cleaner in my situation to return the data from a stored procedure ready to go. There are tons of various solutions out there so use it if you like… I had found this solution on some blog two years ago, I lost the link and I luckily found the code sitting around deep in the bowels of one my code directories...

Here is the table definition:

CREATE TABLE [dbo].[Reference](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](100) NOT NULL,
[Phone] [nvarchar](50) NULL,
[Fax] [nvarchar](50) NULL,
[ParentID] [int] NULL,
[SortOrder] [int] NULL,
[Location] [nvarchar](50) NULL)

The table schema is really simple. ParentID is the ID to the record that is the parent. The SortOrder is the order specific to the level. This will make sense shortly but it is the sort for each level; an example would be:


ID    ParentID    SortOrder
1 NULL 0
2 1 0
3 1 1
4 2 0
5 2 1
6 2 2
7 3 0
8 3 1

So the SortOrder is relative to the grouping the ID is associated to. To select the data so that you get back all the data in the tree and so the data is nicely ordered just like above use this query.

    WITH Tree (id, [name], phone, fax, parentID, sortOrder, location, GroupLevel, SortKey) AS
(
-- Anchor query.
SELECT DISTINCT R.id, R.[name], R.phone, R.fax, R.parentID, R.sortOrder, R.location,
0, CAST(R.sortOrder AS VARBINARY(900))
FROM Referenceas R
WHERE R.parentID IS NULL
UNION ALL

-- Recursive query.
SELECT R.id, R.[name], R.phone, R.fax, R.parentID, R.sortOrder, R.location,
T.GroupLevel+1, CAST(T.SortKey + CAST (R.sortOrder AS BINARY(4)) AS VARBINARY(900))
FROM Referenceas R, Tree as T
WHERE R.parentID = T.id
)
SELECT
T.id,
T.[name],
T.phone,
T.fax,
T.parentID,
T.sortOrder,
T.location,
T.GroupLevel,
T.SortKey
FROM Tree as T
ORDER BY T.SortKey

This is how it works:

  • There is an inner query referred to as the anchor; this will get the root node. One limitation of this query is that there must be a root node. In this case, it is there the parentID IS NULL. However if you do not what to return the root node, you can add WHERE T.parentID IS NOT NULL to the outer query.
  • Then there is the recursive query which used the anchor query.
  • Both the anchor and recursion are based on the sortOrder.
  • The outer query selects data from the two inner queries and orders them based on the SortKey. The SortKey was generated by the two inner queries and will ensure that both the hierarchy and the SortOrder within the results are respected.

No comments: