I am using this query to create HTML table and send via email. Is it possible to merge cells to improve readability only when the "Group ID" and "Total Transaction Sum" columns have the same value? The following 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
The results I got
The results I want
Link to HTML editor https://codebeautify.org/real-time-html-editor/y237bf87d
P粉3109311982024-03-31 14:04:19
To Siggemannen's very good answer, I just want to add an alternative way to handle those td's in xquery,
SELECT GroupID,
AccountID,
Country,
AccountTransactionSum,
TotalTransactionSum = sum(AccountTransactionSum) over (partition by GroupID),
rowspan = COUNT(*) OVER(PARTITION BY GroupID),
display = CASE WHEN lag(GroupID) OVER(ORDER BY GroupID,AccountID) = GroupID THEN 'display:none' ELSE '' END
FROM #list
After defining rowspan and display, you can use them in the xquery for loop
for $row in /root/row let $pos := count(root/row[. << $row]) + 1 return{$pos} {data($row/GroupID)} {data($row/AccountID)} {data($row/Country)} {data($row/AccountTransactionSum)} {data($row/TotalTransactionSum)}
P粉7544734682024-03-31 12:15:21
This is a great question because I had no idea xquery could work 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('
| No. | Group ID | Account ID | Country | Account Transaction Sum | Total Transaction Sum |
|---|---|---|---|---|---|
| {$pos} | {data($row/AccountID)} | {data($row/Country)} | {data($row/AccountTransactionSum)} | ||
| {$pos} | {data($row/GroupID)} | {data($row/AccountID)} | {data($row/Country)} | {data($row/AccountTransactionSum)} | {data($row/TotalTransactionSum)} |
| {$pos} | {data($row/GroupID)} | {data($row/AccountID)} | {data($row/Country)} | {data($row/AccountTransactionSum)} | {data($row/TotalTransactionSum)} |
Basically I created two columns, rowspan and skipTd. The first controls whether the rowspan should be applied, and the second indicates whether the current I then added a nested if to xquery so it returns rowspanned, "skip" or normal HTML based on those two flags. Maybe there is a better way, I'm no expert. should be skipped since it belongs to the same group.