Splitting one row of data into two when exporting a SQL result into HTML

I have a result set in SQL Server (simplified and with fewer rows):

Name	Last Sale	Delivery Details	Call Date	Details
Smith	2024-06-13	Parcels go to side dr	2024-08-19	Enquiry re stk
Smith	2024-06-13	Parcels go to side dr	2024-08-24	Will call back
Smith	2024-06-13	Parcels go to side dr	2024-09-01	Payment Denied
Johnson	2024-09-25	Ring doorbell	2024-09-26	Payment Denied
Johnson	2024-09-25	Ring doorbell	2024-09-30	Payment Denied
Johnson	2024-09-25	Ring doorbell	2024-10-02	Payment Denied
Johnson	2024-09-25	Ring doorbell	2024-10-04	Payment Denied
Johnson	2024-09-25	Ring doorbell	2024-10-15	Payment Denied

Currently, using this code:

set @xhtmlbody = 
(
    select 
    (
        select 
            Name, Community, LastSale, DelDetails, CallDate, Details 
        from #Results2 for xml path('row'),type, root('root')).query
        (
            '<html><head>
            <meta charset="utf-8"/>
            (: including embedded CSS styling :)
            <style>
            table <![CDATA[ {border-collapse: collapse;  width: 300px;} ]]>
            th <![CDATA[ {background-color: #4CAF50; color: white;} ]]>
            th, td <![CDATA[ { text-align: left; padding: 8px;} ]]>
            tr:nth-child(even) <![CDATA[ {background-color: #f2f2f2;} ]]>
            #green <![CDATA[ {background-color: #3366cc;} ]]>
  </style>
         </head>
         <body style="font-family:Arial;">
<table style="width:100%" border="1">
    
   <caption><h1>{sql:variable("@tableCaption")}</h1><h2>Account Activity</h2></caption>
   <thead>
      <tr>
        <th>Name</th>
        <th>Last Sale</th>
        <th>Delivery Details</th>
        <th>Call Date</th>
        <th>Details</th>
       </tr>
   </thead>
   <tbody>
   {
    for $row in /root/row
    return <tr>
            <td>{data($row/Name)}</td>
            <td>{data($row/LastSale)}</td>
            <td>{data($row/DelDetails)}</td>
            <td>{data($row/CallDate)}</td>
            <td>{data($row/Details)}</td>
           </tr>
}
</tbody></table>
</body></html>'
));

This produces the result as you would expect, namely repeating the first three columns and then with a different final two. What I would like is to have the following header info per customer, as in:

Name	Last Sale	Delivery Details
Smith	2024-06-13	Parcels go to side dr

Call Date	Details
2024-08-19	Enquiry re stk
2024-08-24	Will call back
2024-09-01	Payment Denied

Name	Last Sale	Delivery Details
Johnson	2024-09-25	Ring doorbell

Call Date	Details
2024-09-26	Payment Denied
2024-09-30	Payment Denied
2024-10-02	Payment Denied
2024-10-04	Payment Denied
2024-10-15	Payment Denied

How do I go about this? Is it even possible?

I’m a little out of my comfort zone here, so can’t really figure out the next step. I have looked elsewhere for ways to resolve it, but I can’t find anything that suits this case.

You need to sign in to view this answers

Related Post