Skip to content
Advertisement

how to get data grouped by ids

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

Advertisement

Answer

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

db<>fiddle demo

6 People found this is helpful
Advertisement