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
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
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 #listAfter defining rowspan and display, you can use them in the xquery for loop
This is a great question because I had no idea
xquerycould 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('Basically I created two columns, rowspan and skipTd. The first controls whether the rowspan should be applied, and the second indicates whether the current should be skipped since it belongs to the same group.
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.