简体中文（ZH-CN）English（EN）繁体中文（ZH-TW）日本語（JA）한국어（KO）Melayu（MS）Français（FR）Deutsch（DE）
Home
Article
Q&A
Course
Topic
Download
Game
Dictionary
Recent Updates

Home  >  Q&A  >  body text

HTML table merge cells in SQL

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粉005105443P粉005105443186 days ago341

reply all(2)I'll reply

  • P粉310931198

    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)}

    reply
    0
  • P粉754473468

    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('
                                                                            
                                                                            {for $row in /root/row
                                                                            let $pos := count(root/row[. << $row]) + 1

                                                                            return 
                                                                            if ($row/skipTd > 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.

    reply
    0
  • Cancelreply