how to get data grouped by ids-CodePudding

I have grouped data in a temporary table according to id_card. There are multiple activities for each card. I want to send 1 email for all activities happened on that particular card. But, I don't know how to get data according to ids. should i use cursor for fetching the ids ? Example in the table, want 2 rows for id_card =1 and then 2 rows for id_card = 3 and so on

id_card   dat_actitity     type 
1         2022-04-21       tag on           
1         2022-04-23       tag off  
2         2022-04-28       tag on
2         2022-04-20       tag off  
2         2022-04-01       tag on
3         2022-04-27       tag on

Can you please help. Thanks very much

CodePudding user response：

You may use a while loop, something like this

declare @id int

-- get the first id (minimum)
select @id = min(id) from #tmp

while @id is not null
begin 
   -- get the rows
   select * from #tmp where id = @id

   -- send mail
   exec msdb.dbo.sp_send_dbmail . . .

   -- get next id
   select @id = min(id) from #tmp where id > @id
end

CodePudding user response：

declare @id int
-- get the first id (minimum)
select @id = min(id_card) from #tmp

while @id is not null
begin 
   -- get the rows
   select * from #tmp where id_card = @id

   --send email

   -- get next id
    select @id = min(id_card) from #tmp where id_card > @id
end