Let us start by understanding the structure of the recursive CTE:
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
The semantics of the recursive execution is as follows:
With that in mind, and taking this table has an example:
create table a (id int identity (1,1),A nvarchar(max)); insert into a values ('First '); insert into a values ('Second '); insert into a values ('Third '); insert into a values ('Forth ');
id A
--- -------
1 First
2 Second
3 Third
4 Forth
And the following goal:
A0
-------------------------
First Second Third Forth
So let us start with the CTE_query_definition
select id,A,concat( A,lag(A) over (order by id)) A0 from a where id = 1
Witch, by itself returns:
id A A0
--- ------- ------
1 First First
[A0] will concatenate [A] with it's previous result, which is none in this case, since it is the first iteration, where [id] = 1.
Forward to the recursive member:
select a.id,a.A,concat( cte.A0,a.A) A0 from a join cte on a.id=cte.id+1
id A A0
--- ------ -------------
2 Second First Second
On this second iteration, the CTE will be called invoking [id] = 2 returning [A0] as the result of concatenating the previous result with the [A] with id=2
...on and on, and over again...:
;with cte as ( select id,A,concat( A,lag(A) over (order by id)) A0 from a where id = 1 union all select a.id,a.A,concat( cte.A0,a.A) A0 from a join cte on a.id=cte.id+1 ) select id,A,A0 from cte order by id
We end up with the following result set:
id A A0
--- ------ -------------------------
1 First First
2 Second First Second
3 Third First Second Third
4 Forth First Second Third Forth
;with cte as ( select id,A,concat( A,lag(A) over (order by id)) A0 from a where id = 1 union all select a.id,a.A,concat( cte.A0,a.A) A0 from a join cte on a.id=cte.id+1 ) select top (1) A0 from cte order by id desc option (fast 1)
A0
--------------------------
First Second Third Forth
I hope you had fun with this simple "recursive CTE", and this "non XML" way of transforming a result set into a string.
Below you'll find a couple of links that address this issue.
More about CTE:
recursive CTE: https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx
the XML way: http://bradsruminations.blogspot.pt/2009/10/making-list-and-checking-it-twice.html
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)