I have this table and I need to aggregate code1 if total2 <=2. Basically roll it up/combine total to the next value least value so that field total2 will be >2 . How can I do it in t-sql? Thank you for any help. I've been quite stumped on this.

Raw:

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=ad7f47b37d4694411b8a38db07356a7e

Aggregated:

CodePudding user response：

First you need to identify the least 2 rows based on total2. You can use a row_number()

row_number() over(partition by code1 order by total2)

Once you identified the grp, perform the aggregate on it (only for the least 2 rows grp 2)

for total1 and total2, use SUM(), for code2, as it is string, use string aggregate string_agg()

Sample Data :

with cte as
(
    select    *, 
              grp = case when row_number() over(partition by code1 
                                                    order by total2) <= 2
                         then 2
                         else 1
                         end
       from   Test1
)
select code1,
       code2 = case when grp = 1 then max(code2)
                    else string_agg(code2, ' ') within group (order by code2)
                    end,
       total1 = case when grp = 1 then max(total1) else sum(total1) end,
       total2 = case when grp = 1 then max(total2) else sum(total2) end       
from   cte
group by code1, grp
order by code1, grp

Result :

fiddle<> demo