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, Oct 11 '22 at 20:55

score 5 · Accepted Answer · answered Oct 11 '22 at 21:01

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 '22 at 21:01

DannySlor

4,574
1
3
11

1

Masterful work. Thank you! – Coldchain9 Oct 11 '22 at 21:05

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

1 Answers1