May 8, 2012

MS sql what is CTE? how to write recusive query in sql?


A common table expression (CTE) can be thought of as a temporary result set defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.

Pseudocode and semantics

The recursive CTE structure must contain at least
  • one anchor member

    The first invocation of the recursive CTE consists of one ( or more) CTE_query_definition(s). Because these query definitions form the base result set of the CTE structure, they are referred to as anchor members. All anchor-member query definitions must be positioned before the first recursive member definition, and a UNION ALL operator must be used to join the last anchor member with the first recursive member.
  • one recursive member.

    The recursive invocation includes one or more CTE_query_definitions joined by UNION ALL operators that reference the CTE itself. These query definitions are referred to as recursive members.
The following pseudocode shows the components of a simple recursive CTE that contains a single anchor member and single recursive member.
WITH cte_name ( column_name [,...n] )
AS
(
CTE_query_definition –- Anchor member is defined.
UNION ALL
CTE_query_definition –- Recursive member is defined referencing cte_name.
)

-- Statement using the CTE
SELECT *
FROM cte_name
Example
Let's demontrate the Category table example, we have table with different categories, sub-categories, sub-sub-categroies etc... to Nth level.
We have the following data in our Category table:

CategoryID Name ParentID
1 Office NULL
2 Computer NULL
3 Books NULL
4 Office Accessories 1
5 Furniture 1
6 Decoration Items 1
7 LCDs 2
8 CPUs 2
9 Keyboards 2
10 Urdu 3
11 Programming 3
12 ASP.Net 11
13 C# 11
14 VB.Net 11
15 Java 11
16 PHP 11
17 Poetry 10
18 Stories 10
19 Novels 10
20 Kids 18
21 Funny 18
We are interested to get the full category names (from parent category to last child category, e.g. Books > Programming > C# ), we acheive the result implementing the same CTE pseudocode.
WITH CategoryList
AS
(
-- Anchor Query Member
SELECT parent.CategoryID, CONVERT(VARCHAR(50), Parent.Name) as Name, parent.ParentID
FROM Category as parent
WHERE parent.ParentID IS NULL

UNION ALL

-- Recursive Query Member
SELECT child.CategoryID, CONVERT(VARCHAR(50), CL.Name + ' > ' + child.Name) as Name, child.ParentID
FROM Category as child
INNER JOIN CategoryList as CL ON child.ParentID = CL.CategoryID -- Can be think of as the termination condition
WHERE child.ParentID IS NOT NULL
)

SELECT *
FROM CategoryList
That's simple, you will get the required output as:

CategoryID Name ParentID
1 Office NULL
2 Computer NULL
3 Books NULL
10 Books > Urdu 3
11 Books > Programming 3
12 Books > Programming > ASP.Net 11
13 Books > Programming > C# 11
14 Books > Programming > VB.Net 11
15 Books > Programming > Java 11
16 Books > Programming > PHP 11
17 Books > Urdu > Poetry 10
18 Books > Urdu > Stories 10
19 Books > Urdu > Novels 10
20 Books > Urdu > Stories > Kids 18
21 Books > Urdu > Stories > Funny 18
7 Computer > LCDs 2
8 Computer > CPUs 2
9 Computer > Keyboards 2
4 Office > Office Accessories 1
5 Office > Furniture 1
6 Office > Decoration Items 1

References

http://msdn.microsoft.com/en-us/library/ms175972.aspx