Pivot resultset into one line list using recursive cte

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

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

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.

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