Convert Rows into A Delimited String, Part 3

Here's another sample of dataset rows converted into delimited string based on a unique ID. Looking at the sample data is better than explaining how this works... Populate sample data:

declare @tbl_email table (id int, email varchar(20))
insert into @tbl_email
select 1, 'A'
union all
select 2, 'A0'
union all
select 2, 'A1'
union all
select 3, 'A2'
union all
select 3, 'A3'
union all
select 3, 'A4'
union all
select 4, 'A5'

select * from @tbl_email

id          email
----------- --------------------
1           A
2           A0
2           A1
3           A2
3           A3
3           A4
4           A5

Here's the required output:

id          email_list
----------- -----------
1           A
2           A0, A1
3           A2, A3, A4
4           A5

And the code...

;with Numbered
as 
   (
      select       
         rownum = row_number() over(partition by id order by id, email),
         id, email
      from @tbl_email
   )
,FirstRecords
as 
   (
      select rownum, id, email from Numbered where rownum = 1
   )
,ReturnAllRecords
as
   (
      select rownum, id, cast(email as varchar(500)) as email_list from FirstRecords  
      union all      
      select Numbered.rownum, Numbered.id,  cast(email_list + ', ' + Numbered.email as varchar(500))
      from Numbered
         inner join ReturnAllRecords 
            on ReturnAllRecords.id = Numbered.id and
               ReturnAllRecords.rownum + 1 = Numbered.Rownum
   )
select id, max(email_list) as email_list
from ReturnAllRecords
group by id

Comments are always welcome...


 ~~ CK