# Pivot resultset into one line list using recursive cte

Published:
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs).
Being someone who is always looking for alternatives to "work your data", I came across this solution, while refreshing my recursive CTE skills.

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:

1. Split the CTE expression into anchor and recursive members.
2. Run the anchor member(s) creating the first invocation or base result set (T0).
3. Run the recursive member(s) with Ti as an input and Ti+1 as an output.
4. Repeat step 3 until an empty set is returned.
5. Return the result set. This is a UNION ALL of T0 to Tn.

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

```    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

## cleaning it up

```    ;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.