简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)
HTML table merge cells in SQL
P粉005105443
P粉005105443 2024-03-30 16:45:17
0
2
339

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粉005105443
P粉005105443

reply all(2)
P粉310931198
P粉3109311982024-03-31 14:04:19 2 floor

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[. 
 {$pos}
 {data($row/GroupID)}
 {data($row/AccountID)}
 {data($row/Country)}
 {data($row/AccountTransactionSum)}
 {data($row/TotalTransactionSum)}
Like +0
Add Reply
P粉754473468
P粉7544734682024-03-31 12:15:21 1 floor

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('
{for $row in /root/row let $pos := count(root/row[. 0) then else if ($row/rowspan > 1) then else }
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)}
')); SELECT @xmlBody

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.

Like +0
Add Reply
Popular Topics
More>
Popular Articles
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn：Public welfare online PHP training，Help PHP learners grow quickly！