Using string_agg() over n rows of data grouped by a Common Key

Question

I would like to use string_agg(column_name,',') on some IDs in a column from a SQL result set. I would be grouping by a single key, but also want to make sure each grouped set results in n rows being fed into the string_agg(column_name,',') function

Example:

create table #temp (first_name varchar(20), data_id char(3))
insert into #temp (first_name, data_id) values('jeff', 'XX1')
insert into #temp (first_name, data_id) values('jeff', 'X23')
insert into #temp (first_name, data_id) values('jeff', 'X87')
insert into #temp (first_name, data_id) values('jeff', 'X09')
insert into #temp (first_name, data_id) values('jeff', 'X15')

insert into #temp (first_name, data_id) values('bob', 'X76')
insert into #temp (first_name, data_id) values('bob', 'X17')
insert into #temp (first_name, data_id) values('bob', 'X98')
insert into #temp (first_name, data_id) values('bob', 'X99')

select * from #temp

first_name  data_id
      jeff      XX1
      jeff      X23
      jeff      X87
      jeff      X09
      jeff      X15
       bob      X76
       bob      X17
       bob      X98
       bob      X99

Ideal result I am trying to create for lets say, n = 3 rows string aggregated per group is below. in the event that there are <3 rows left for that particular first_name key, throw the remaining into the column.

I am trying to use a recursive CTE but can't quite wrap my head around it

first_name        data_id
      jeff    XX1,X23,X87
      jeff        X09,X15
       bob    X76,X17,X98
       bob            X99

@DannySlor nope, the actual numbers have no meaning here. I'll edit the question to clear up the confusion. My early thought was to try a row_number() over (partition by first_name order by first_name) but couldn't quite fill in the blanks to get the solution — Coldchain9
– Coldchain9, Commented Oct 11, 2022 at 20:55

DannySlor · Accepted Answer · 2022-10-11 21:01:40Z

5

select  first_name
       ,string_agg(data_id, ',') as data_id
from    (
        select  *
                ,(row_number() over(partition by first_name order by data_id)-1)/3 as grp
        from     #temp
        ) t
group by first_name, grp
order by first_name

first_name	data_id
bob	X17,X76,X98
bob	X99
jeff	X09,X15,X23
jeff	X87,XX1

Fiddle

answered Oct 11, 2022 at 21:01

DannySlor

4,6201 gold badge5 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Coldchain9 Over a year ago

Masterful work. Thank you!

Collectives™ on Stack Overflow

Using string_agg() over n rows of data grouped by a Common Key

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related