Merge cells in HTML table in SQL

Luna
February 24, 2023
193 views
0 votes
2 Answers

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

Answers

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.

For Siggemannen’s very good answer i just want to add an alternative way to deal with those td’s inside 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 rowspan and display defined you can use them in your xquery for loop

for $row in /root/row
let $pos := count(root/row[. << $row]) + 1
return 
<tr align="center" valign="center" id="{data($row/TotalCount)}">
 <td>{$pos}</td>
 <td style="{data($row/display )}" rowspan="{data($row/rowspan)}">{data($row/GroupID)}</td>
 <td>{data($row/AccountID)}</td>
 <td>{data($row/Country)}</td>
 <td>{data($row/AccountTransactionSum)}</td>
 <td style="{data($row/display )}" rowspan="{data($row/rowspan)}">{data($row/TotalTransactionSum)}</td>
</tr>

Please signup or login to give your own answer.

Click here to cancel reply.