Pivot resultset into one line list using recursive cte

x-menIT super hero
CERTIFIED EXPERT
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




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


0
1,966 Views
x-menIT super hero
CERTIFIED EXPERT

Comments (0)

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.