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