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)