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