<

Go Premium for a chance to win a PS4. Enter to Win

x

Pivot resultset into one line list using recursive cte

Published on
3,223 Points
223 Views
Last Modified:
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
Comment
Author:x-men
0 Comments

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Join & Write a Comment

Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Other articles by this author

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month