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.
EDIT - I should add that this is to then generate an email using SQL server, so the splitting of the queries up seems to be impossible?
Solution
Please try the following solution.
It simulates grouping by finding unique person names via XQuery distinct-values() function.
The rest is leveraging XQuery FLWOR expressions to compose XHTML tables per person name on the fly.
Also, it is possible to compose XHTML with one single table just alternating thead and tbody sections.
SQL
-- DDL and sample data population, start
DECLARE @tbl TABLE (Name VARCHAR(20), [LastSale] DATE, [DeliveryDetails] VARCHAR(30), [CallDate] DATE, Details VARCHAR(30));
INSERT INTO @tbl (Name, [LastSale], [DeliveryDetails], [CallDate], Details) VALUES
('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')
-- DDL and sample data population, end
DECLARE @xhtmlbody XML;
set @xhtmlbody =
(
select
(
SELECT Name, LastSale, [DeliveryDetails], CallDate, Details
FROM @tbl
ORDER BY Name
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;">
{
for $person in distinct-values(/root/row/Name)
return
(<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>
</tr>
</thead>
<tbody>
{
for $row in /root/row[(Name/text())[1] eq $person][1]
return <tr>
<td>{data($row/Name)}</td>
<td>{data($row/LastSale)}</td>
<td>{data($row/DeliveryDetails)}</td>
</tr>
}
</tbody></table>,
<table>
<thead>
<tr>
<th>Call Date</th>
<th>Details</th>
</tr>
</thead>
<tbody>
{
for $row in /root/row[(Name/text())[1] eq $person]
return <tr>
<td>{data($row/CallDate)}</td>
<td>{data($row/Details)}</td>
</tr>
}
</tbody>
</table>)
}
</body></html>'
));
SELECT @xhtmlbody;
Output
<html>
<head>
<meta charset="utf-8" />
<style>
table {border-collapse: collapse; width: 300px;}
th {background-color: #4CAF50; color: white;}
th, td { text-align: left; padding: 8px;}
tr:nth-child(even) {background-color: #f2f2f2;}
#green {background-color: #3366cc;}
</style>
</head>
<body style="font-family:Arial;">
<table style="width:100%" border="1">
<thead>
<tr>
<th>Name</th>
<th>Last Sale</th>
<th>Delivery Details</th>
</tr>
</thead>
<tbody>
<tr>
<td>Johnson</td>
<td>2024-09-25</td>
<td>Ring doorbell</td>
</tr>
</tbody>
</table>
<table>
<thead>
<tr>
<th>Call Date</th>
<th>Details</th>
</tr>
</thead>
<tbody>
<tr>
<td>2024-09-26</td>
<td>Payment Denied</td>
</tr>
<tr>
<td>2024-09-30</td>
<td>Payment Denied</td>
</tr>
<tr>
<td>2024-10-02</td>
<td>Payment Denied</td>
</tr>
<tr>
<td>2024-10-04</td>
<td>Payment Denied</td>
</tr>
<tr>
<td>2024-10-15</td>
<td>Payment Denied</td>
</tr>
</tbody>
</table>
<table style="width:100%" border="1">
<thead>
<tr>
<th>Name</th>
<th>Last Sale</th>
<th>Delivery Details</th>
</tr>
</thead>
<tbody>
<tr>
<td>Smith</td>
<td>2024-06-13</td>
<td>Parcels go to side dr</td>
</tr>
</tbody>
</table>
<table>
<thead>
<tr>
<th>Call Date</th>
<th>Details</th>
</tr>
</thead>
<tbody>
<tr>
<td>2024-08-19</td>
<td>Enquiry re stk</td>
</tr>
<tr>
<td>2024-08-24</td>
<td>Will call back</td>
</tr>
<tr>
<td>2024-09-01</td>
<td>Payment Denied</td>
</tr>
</tbody>
</table>
</body>
</html>
- Using Dapper one-to-many (spliton) with generics
- How to join two SELECT queries
- Join two SELECT queries having same column names but different row values in both tables
- How can you represent inheritance in a database?
- String_agg for SQL Server before 2017
- How can I remove accents on a string?
- SQL - order by list order
- Convert varchar list to int in Sql Server
- "č" converted to "c" when querying through SQL Server 2008
- SQL Server 2016, Invalid object name 'STRING_SPLIT'
- SQL: Data Transpose
- How do I create a unique key that is case sensitive?
- ODBC Data Sources; can't add MySQL Driver
- SQL Server - how to query JSON string which has numeric value as keys
- BizTalk 2020 WCF-Sql Polling Receive Location no Response
- Pyodbc: Login Timeout Error
- Unable to upload rdl SSRS 2016
- Database's backup location that created by Azure Data Studio on Mac M1
- Entity Framework: The instance of entity type '<T>' cannot be tracked because another instance with the key value '{Id: x}' is already being tracked
- Check if a connection is in a transaction
- Can I have multiple rows of tabs in SQL Server Management Studio
- SQL Server: Make all UPPER case to Proper Case/Title Case
- How to disable transactions for individual migrations in Flyway on SQL Server
- Natural (human alpha-numeric) sort in Microsoft SQL 2005
- SQL how to do a LIKE search on each value of a declared variable
- Roll Up with column changes
- How to open a huge .sql file
- ExecuteReader TimeOut solved by changing the name of the stored procedure
- How do I get list of all tables in a database using TSQL?
- SQL Database in restoring mode