how to get data grouped by ids

Question

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

Answer 1

select @id = min(id_card) from #tmp
while @id is not null
begin 
 drop table if exists  #rows 
   -- get the rows
   select * into #rows from #tmp where id_card = @id
 
   if (@@ROWCOUNT > 0)
   begin 
   -- send mail
 SET @Subject = 'Flagged Card activity noticed on Card with ID ' + convert(varchar(10), @id) 
 SET @Message =
                    N'<H3>Details of the activity are as follows:</H3>' +
                    N'<table border="1">' +
                    N'<tr bgcolor=#ADD6FF>' +                   
                    N'<th>Activity Date</th>' +
                    N'<th>Activity Day</th>' +
                    N'<th>Time</th>' +                  
                    N'<th>Activity Type</th>' +             
                    N'</tr>' +                  
                    CAST(( 
                    SELECT  
                            td = Convert(varchar(10), dat_activity, 103), '',
                            td = FORMAT(dat_activity,'ddd'), '',
                            td = Convert(varchar(8), Convert(time, dat_activity)), '',                                  
                            td = ISNULL(str_activitydesc,'N/A')                     
                            
                            FROM #tmp
                            order by dat_activity desc
                            FOR XML PATH('tr'), ELEMENTS) AS NVARCHAR(MAX)) +
                        N'</table>' 
        PRINT 'Email Sent'
        --Now send the email
        EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'Profile',
            @recipients = @recipientList,
            @subject = @Subject,
            @body = @Message,
            @body_format = 'HTML',
            @importance = 'High'
  end
   -- get next id
   select @id = min(id_card) from #tmp where id_card > @id
end

Answer 2

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