I am using this query to create an HTML table and send it by email. Is there any way to merge the cells for the Group ID and Total Transaction Sum columns only if they have the same value to improve readability? Below is the result I want to get
CREATE TABLE #list (GroupID int,AccountID int,Country varchar (20),AccountTransactionSum int)
Insert into #list
values
(1,18754,'United Kingdom',110),
(1,24865,'Germany',265),
(1,82456,'Poland',1445),
(1,98668,'United Kingdom',60),
(1,37843,'France',1490),
(2,97348,'United Kingdom',770)
DECLARE @xmlBody XML
SET @xmlBody = (SELECT (SELECT GroupID, AccountID, Country, AccountTransactionSum, TotalTransactionSum = sum(AccountTransactionSum) over (partition by GroupID)
FROM #list
ORDER BY GroupID
FOR XML PATH('row'), TYPE, ROOT('root')).query('<html><head><meta charset="utf-8"/><style>
table <![CDATA[ {border-collapse: collapse; } ]]>
th <![CDATA[ {background-color: #4CAF50; color: white;} ]]>
th, td <![CDATA[ { text-align: center; padding: 8px;} ]]>
tr:nth-child(even) <![CDATA[ {background-color: #f2f2f2;} ]]>
</style></head>
<body><table border="1" cellpadding="10" style="border-collapse:collapse;">
<thead><tr>
<th>No.</th>
<th> Group ID </th><th> Account ID </th><th> Country </th><th> Account Transaction Sum </th><th> Total Transaction Sum </th>
</tr></thead>
<tbody>
{for $row in /root/row
let $pos := count(root/row[. << $row]) + 1
return <tr align="center" valign="center">
<td>{$pos}</td>
<td>{data($row/GroupID)}</td><td>{data($row/AccountID)}</td><td>{data($row/Country)}</td><td>{data($row/AccountTransactionSum)}</td><td>{data($row/TotalTransactionSum)}</td>
</tr>}
</tbody></table></body></html>'));
select @xmlBody
result I have
result I would like to have
link to the HTML editor https://codebeautify.org/real-time-html-editor/y237bf87d
This was a great question, because I didn't know xquery
could do this magic!
This is what I came up with:
DROP TABLE #list
go
SELECT *
INTO #list
FROM (
VALUES
(1,18754,'United Kingdom',110),
(1,24865,'Germany',265),
(1,82456,'Poland',1445),
(1,98668,'United Kingdom',60),
(1,37843,'France',1490),
(2,97348,'United Kingdom',770)
) t (groupid,accountid, country, AccountTransactionSum)
DECLARE @xmlBody XML
SET @xmlBody = (SELECT (SELECT GroupID,
AccountID,
Country,
AccountTransactionSum,
TotalTransactionSum = sum(AccountTransactionSum) OVER (partition BY GroupID),
COUNT(*) OVER(PARTITION BY GroupID) AS rowspan,
CASE WHEN lag(GroupID) OVER(ORDER BY groupid,accountid) = GroupID THEN 1 ELSE 0 END AS skipTd
FROM #list ll
ORDER BY GroupID, accountid
FOR XML PATH('row'), TYPE, ROOT('root')).query('<html><head><meta charset="utf-8"/><style>
table <![CDATA[ {border-collapse: collapse; } ]]>
th <![CDATA[ {background-color: #4CAF50; color: white;} ]]>
th, td <![CDATA[ { text-align: center; padding: 8px;} ]]>
tr:nth-child(even) <![CDATA[ {background-color: #f2f2f2;} ]]>
</style></head>
<body><table border="1" cellpadding="10" style="border-collapse:collapse;">
<thead><tr>
<th>No.</th>
<th> Group ID </th><th> Account ID </th><th> Country </th><th> Account Transaction Sum </th><th> Total Transaction Sum </th>
</tr></thead>
<tbody>
{for $row in /root/row
let $pos := count(root/row[. << $row]) + 1
return
if ($row/skipTd > 0) then
<tr align="center" valign="center">
<td>{$pos}</td>
<td>{data($row/AccountID)}</td>
<td>{data($row/Country)}</td>
<td>{data($row/AccountTransactionSum)}</td>
</tr>
else
if ($row/rowspan > 1) then
<tr align="center" valign="center">
<td>{$pos}</td>
<td rowspan ="{data($row/rowspan)}">{data($row/GroupID)}</td>
<td>{data($row/AccountID)}</td>
<td>{data($row/Country)}</td>
<td>{data($row/AccountTransactionSum)}</td>
<td rowspan ="{data($row/rowspan)}">{data($row/TotalTransactionSum)}</td>
</tr>
else
<tr align="center" valign="center">
<td>{$pos}</td>
<td>{data($row/GroupID)}</td>
<td>{data($row/AccountID)}</td>
<td>{data($row/Country)}</td>
<td>{data($row/AccountTransactionSum)}</td>
<td>{data($row/TotalTransactionSum)}</td>
</tr>
}
</tbody></table></body></html>'));
SELECT @xmlBody
Basically I create two columns, rowspan and skipTd. First controls if rowspan should be applicable, and second says if the current <td>
should be skipped because it's part of same group.
Then I added a nested if to the xquery so it returns either rowspanned, "skipped" or normal HTML depending on these two flags. Maybe there's a nicer way to do it, I'm no expert.