<

Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x

Pivot resultset into one line list using recursive cte

Published on
3,334 Points
334 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Join & Write a Comment

Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Other articles by this author
Suggested Courses

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month