Geeks With Blogs
Thomas Pepe

CTEs or common table expressions can be used to make recursive SQL stantements.  CTEs are ANSI-99 standard.  Please read www.sql-server-performance.com/2005/cte-sql2005/ for more information.

I have only had 2 instances in my career where CTEs made sense for me to use in a pratical setting.  If you have found them more practical, please share.  The first time I needed a CTE I was displaying corporate branches from a recurssive table.  Some branches had branches, which had branches, and so on, whereas other branches ended after only a node or two.  I found many articles on displaying something like this:

category 1 > sub category 1 > sub sub category...
category 1 > sub category 2...

I found many articles online on how to do this.  If this is what you need a simple google search should get you on the right path.  The second use I have below.  I needed all of the text from a particular group concatenated together.  The closest match I could find to guide me was Carl Anderson's article on concatenating rows here: http://www.sqlservercentral.com/articles/T-SQL/67973/ .  It was almost exactly what I needed.  Since the code below is based on it  you should read that article first.  I needed a column not only to be concatenated but to be concatenated per group.  So I wrote the SQL statement below and decided to share it with you.

PS - There is always room for improvement in my code expamples so if you see something that could be better please inform me so that my instructions may be more helpful to others.  Enjoy.

declare @t as table(

      group_id int NOT NULL,

      txt varchar(max) NOT NULL,

      id int NOT NULL

      )

 

insert into @t select 0, 'Hello', 0 union select 0, ' Wor', 1 union select 0, 'ld', 2 union select 1, 'another message', 3

--insert into @t select 0, 'Apple', 0 union select 0, 'Banana', 1 union select 0, 'Grape', 2 union select 1, 'Orange', 3

 

 

;with base_cte as(

      select

                  t.txt as value,

                  t.id,

                  (

                        select top 1 id from(

                        (

                              select top 1 t2.id from @t t2

                              where t2.group_id = t.group_id AND t2.id > t.id

                              order by t2.id asc

                        )

                        union all

                              (select -1)

                        )tbl

                        order by id desc             

                  ) as nex_id,

                  t.group_id,

                  case when EXISTS(

                              select top 1 t2.id from @t t2

                              where t2.group_id = t.group_id AND t2.id < t.id

                              order by t2.id asc

                  ) then 0 else 1 end as is_terminal_value,

            (select COUNT(distinct id) from @t t2 where t2.group_id = t.group_id) as record_count_by_group,

            1 as terminator

      from @t t

)

, recurssive_cte as(

      select

            value as value_group_start,

            id,

            group_id,

            record_count_by_group,

            terminator,

            row_number() over(order by group_id, id, terminator) as row_num

      from base_cte cte

      union all

      select

            cur.value_group_start + (select nex.value from base_cte nex

                        where nex.group_id = cur.group_id and

                        nex.id = cur.row_num

                  ) as value_group_start,

            id + 1,

            group_id,

            record_count_by_group,

            terminator + 1,

            row_num + 1 as row_num

      from recurssive_cte as cur

      where

      group_id = (select group_id from base_cte nex

                        where nex.group_id = cur.group_id and

                        nex.id = cur.row_num

        )

      and row_num <= record_count_by_group

)

, grouped_cte as (

      select max(value_group_start) as items

      from recurssive_cte

      where terminator = record_count_by_group

      group by group_id

)

--select * from base_cte

--select * from recurssive_cte

select * from grouped_cte 

Posted on Wednesday, July 27, 2011 12:13 PM SQL | Back to top


Comments on this post: Using CTE to concatenate rows by group

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © tom | Powered by: GeeksWithBlogs.net