Merge HTML table rows in SQL server

fdx2calv  于 2023-10-15  发布在  SQL Server
关注(0)|答案(1)|浏览(128)

I am generating the following string in SQL Server which consists of pay data of employees, no.of rows keep varying according to the pay components each employee gets. Even pay types are not fixed for any two given employees. Almost all the pay components vary according to the employees experience, position, qualification, etc.,

<table style="color: #000066; border-collapse: collapse; font-family: Arial,sans-serif; width: 100%; font-size: 10.0pt;" border="1" cellpadding="5">
<tbody>
<tr style="text-align: center;">
<th>Pay Type</th>
<th>Pay Desc</th>
<th>Pay Freq</th>
<th>Currency</th>
<th>Amount</th>
<th>Per Annum</th>
</tr>
<tr>
<td>Salary</td>
<td>Basic Salary</td>
<td style="text-align: center;">Monthly</td>
<td style="text-align: center;">INR</td>
<td style="text-align: center;">23750.00</td>
<td style="text-align: center;">285000.00</td>
</tr>
<tr>
<td>Salary</td>
<td>House Rent Allowance</td>
<td style="text-align: center;">Monthly</td>
<td style="text-align: center;">INR</td>
<td style="text-align: center;">11875.00</td>
<td style="text-align: center;">142500.00</td>
</tr>
<tr>
<td>Salary</td>
<td>Conveyance Allowance</td>
<td style="text-align: center;">Monthly</td>
<td style="text-align: center;">INR</td>
<td style="text-align: center;">1600.00</td>
<td style="text-align: center;">19200.00</td>
</tr>
<tr>
<td>Salary</td>
<td>Education Allowance</td>
<td style="text-align: center;">Monthly</td>
<td style="text-align: center;">INR</td>
<td style="text-align: center;">200.00</td>
<td style="text-align: center;">2400.00</td>
</tr>
<tr>
<td>Salary</td>
<td>Fp Bal</td>
<td style="text-align: center;">Monthly</td>
<td style="text-align: center;">INR</td>
<td style="text-align: center;">34550.00</td>
<td style="text-align: center;">414600.00</td>
</tr>
<tr>
<td>Salary</td>
<td>Ecal</td>
<td style="text-align: center;">Yearly</td>
<td style="text-align: center;">INR</td>
<td style="text-align: center;">144000.00</td>
<td style="text-align: center;">144000.00</td>
</tr>
<tr>
<td>Salary</td>
<td>Retention Pay</td>
<td style="text-align: center;">Yearly</td>
<td style="text-align: center;">INR</td>
<td style="text-align: center;">105000.00</td>
<td style="text-align: center;">105000.00</td>
</tr>
<tr>
<td>Reimbursements</td>
<td>Medical Reimb. Normal</td>
<td style="text-align: center;">Monthly</td>
<td style="text-align: center;">INR</td>
<td style="text-align: center;">25000.00</td>
<td style="text-align: center;">300000.00</td>
</tr>
<tr>
<td>Reimbursements</td>
<td>Phone, Datacard, Landline</td>
<td style="text-align: center;">Monthly</td>
<td style="text-align: center;">INR</td>
<td style="text-align: center;">2800.00</td>
<td style="text-align: center;">33600.00</td>
</tr>
<tr>
<td>Reimbursements</td>
<td>Phone</td>
<td style="text-align: center;">Yearly</td>
<td style="text-align: center;">INR</td>
<td style="text-align: center;">1000.00</td>
<td style="text-align: center;">1000.00</td>
</tr>
<tr>
<td>Reimbursements</td>
<td>Datacard</td>
<td style="text-align: center;">Yearly</td>
<td style="text-align: center;">INR</td>
<td style="text-align: center;">833.00</td>
<td style="text-align: center;">833.00</td>
</tr>
<tr>
<td>Reimbursements</td>
<td>Spectacle</td>
<td style="text-align: center;">Yearly</td>
<td style="text-align: center;">INR</td>
<td style="text-align: center;">2500.00</td>
<td style="text-align: center;">2500.00</td>
</tr>
<tr>
<td>Benefits</td>
<td>Car Valuation (New Car Scheme)</td>
<td style="text-align: center;">Monthly</td>
<td style="text-align: center;">INR</td>
<td style="text-align: center;">3500.00</td>
<td style="text-align: center;">42000.00</td>
</tr>
<tr>
<td>Benefits</td>
<td>Leave Travel Allowance</td>
<td style="text-align: center;">Monthly</td>
<td style="text-align: center;">INR</td>
<td style="text-align: center;">2084.00</td>
<td style="text-align: center;">25008.00</td>
</tr>
<tr>
<td>Benefits</td>
<td>Bonus</td>
<td style="text-align: center;">Monthly</td>
<td style="text-align: center;">INR</td>
<td style="text-align: center;">12584.00</td>
<td style="text-align: center;">151008.00</td>
</tr>
<tr>
<td>Benefits</td>
<td>Esop</td>
<td style="text-align: center;">Yearly</td>
<td style="text-align: center;">INR</td>
<td style="text-align: center;">282716.00</td>
<td style="text-align: center;">282716.00</td>
</tr>
<tr>
<td>Retirement Benefits</td>
<td>Employee Pf</td>
<td style="text-align: center;">Monthly</td>
<td style="text-align: center;">INR</td>
<td style="text-align: center;">2850.00</td>
<td style="text-align: center;">34200.00</td>
</tr>
<tr>
<td>Retirement Benefits</td>
<td>Employee Gratuity</td>
<td style="text-align: center;">Monthly</td>
<td style="text-align: center;">INR</td>
<td style="text-align: center;">1000.00</td>
<td style="text-align: center;">12000.00</td>
</tr>
</tbody>
</table>

If you observe column one, most of the data is repetative. Is it possible to merge the rows of first column depending on the data it contains in sql server. So that the string looks like below.

<table style="color: #000066; border-collapse: collapse; font-family: Arial,sans-serif; width: 100%; font-size: 10.0pt;" border="1" cellpadding="5">
<tbody>
<tr style="text-align: center;">
<th>Pay Type</th>
<th>Pay Desc</th>
<th>Pay Freq</th>
<th>Currency</th>
<th>Amount</th>
<th>Per Annum</th>
</tr>
<tr>
<td  rowspan="7">Salary</td>
<td>Basic Salary</td>
<td style="text-align: center;">Monthly</td>
<td style="text-align: center;">INR</td>
<td style="text-align: center;">23750.00</td>
<td style="text-align: center;">285000.00</td>
</tr>
<tr>
<td>House Rent Allowance</td>
<td style="text-align: center;">Monthly</td>
<td style="text-align: center;">INR</td>
<td style="text-align: center;">11875.00</td>
<td style="text-align: center;">142500.00</td>
</tr>
<tr>
<td>Conveyance Allowance</td>
<td style="text-align: center;">Monthly</td>
<td style="text-align: center;">INR</td>
<td style="text-align: center;">1600.00</td>
<td style="text-align: center;">19200.00</td>
</tr>
<tr>
<td>Education Allowance</td>
<td style="text-align: center;">Monthly</td>
<td style="text-align: center;">INR</td>
<td style="text-align: center;">200.00</td>
<td style="text-align: center;">2400.00</td>
</tr>
<tr>
<td>Fp Bal</td>
<td style="text-align: center;">Monthly</td>
<td style="text-align: center;">INR</td>
<td style="text-align: center;">34550.00</td>
<td style="text-align: center;">414600.00</td>
</tr>
<tr>
<td>Ecal</td>
<td style="text-align: center;">Yearly</td>
<td style="text-align: center;">INR</td>
<td style="text-align: center;">144000.00</td>
<td style="text-align: center;">144000.00</td>
</tr>
<tr>
<td>Retention Pay</td>
<td style="text-align: center;">Yearly</td>
<td style="text-align: center;">INR</td>
<td style="text-align: center;">105000.00</td>
<td style="text-align: center;">105000.00</td>
</tr>
<tr>
<td rowspan="5">Reimbursements</td>
<td>Medical Reimb. Normal</td>
<td style="text-align: center;">Monthly</td>
<td style="text-align: center;">INR</td>
<td style="text-align: center;">25000.00</td>
<td style="text-align: center;">300000.00</td>
</tr>
<tr>
<td>Phone, Datacard, Landline</td>
<td style="text-align: center;">Monthly</td>
<td style="text-align: center;">INR</td>
<td style="text-align: center;">2800.00</td>
<td style="text-align: center;">33600.00</td>
</tr>
<tr>
<td>Phone</td>
<td style="text-align: center;">Yearly</td>
<td style="text-align: center;">INR</td>
<td style="text-align: center;">1000.00</td>
<td style="text-align: center;">1000.00</td>
</tr>
<tr>
<td>Datacard</td>
<td style="text-align: center;">Yearly</td>
<td style="text-align: center;">INR</td>
<td style="text-align: center;">833.00</td>
<td style="text-align: center;">833.00</td>
</tr>
<tr>
<td>Spectacle</td>
<td style="text-align: center;">Yearly</td>
<td style="text-align: center;">INR</td>
<td style="text-align: center;">2500.00</td>
<td style="text-align: center;">2500.00</td>
</tr>
<tr>
<td rowspan="4">Benefits</td>
<td>Car Valuation (New Car Scheme)</td>
<td style="text-align: center;">Monthly</td>
<td style="text-align: center;">INR</td>
<td style="text-align: center;">3500.00</td>
<td style="text-align: center;">42000.00</td>
</tr>
<tr>
<td>Leave Travel Allowance</td>
<td style="text-align: center;">Monthly</td>
<td style="text-align: center;">INR</td>
<td style="text-align: center;">2084.00</td>
<td style="text-align: center;">25008.00</td>
</tr>
<tr>
<td>Bonus</td>
<td style="text-align: center;">Monthly</td>
<td style="text-align: center;">INR</td>
<td style="text-align: center;">12584.00</td>
<td style="text-align: center;">151008.00</td>
</tr>
<tr>
<td>Esop</td>
<td style="text-align: center;">Yearly</td>
<td style="text-align: center;">INR</td>
<td style="text-align: center;">282716.00</td>
<td style="text-align: center;">282716.00</td>
</tr>
<tr>
<td rowspan="2">Retirement Benefits</td>
<td>Employee Pf</td>
<td style="text-align: center;">Monthly</td>
<td style="text-align: center;">INR</td>
<td style="text-align: center;">2850.00</td>
<td style="text-align: center;">34200.00</td>
</tr>
<tr>
<td>Employee Gratuity</td>
<td style="text-align: center;">Monthly</td>
<td style="text-align: center;">INR</td>
<td style="text-align: center;">1000.00</td>
<td style="text-align: center;">12000.00</td>
</tr>
</tbody>
</table>

Following is my query to generate this string.

set @tabSalary=N'<table cellpadding="5" style="color:#000066;border-collapse: collapse;font-family:Arial,sans-serif;width:100%;font-size: 10.0pt;" border="1">'
    + N'<tr style="text-align:center;"><th>Pay Type</th><th>Pay Desc</th><th>Pay Freq</th><th>Currency</th><th>Amount</th>
    <th>Per Annum</th>
    </tr>'
    + CAST((
        SELECT  isnull(p.PayTypeDesc,'') AS td,
                isnull(dbo.ProperCase(p.PayDesc),'') AS td,
                isnull(p.PayFrequency,'')  as tda,
                 isnull(p.Currency,'') as tda,
                isnull(cast(p.PerMonth as varchar),'') as tda,
                isnull(cast(p.PerAnnum as varchar),'') as tda
        FROM   #saltmp p
        order by p.sort1, p.sort2
        FOR XML RAW('tr'), ELEMENTS
    ) AS NVARCHAR(MAX))
    + N'</table>'
    SET @tabSalary = REPLACE(@tabSalary, '<tda>', '<td style="text-align:center;">')
    SET @tabSalary = REPLACE(@tabSalary, '</tda>', '</td>')
sql-server

1条答案

按热度按时间
mbskvtky

mbskvtky1#

Below is my solution. Quite complex, though.

A few notes before that:

  • i would personally not recommend this kind of processings within SQL Server, because it is not optimized for them
  • this should be done on your front-end, because of maintainability and flexibility of your HTML, CSS, event-handlig, etc...
  • nevertheless, such queries are a challenge from SQL perspective, so hopefully this may help you and others as well
DECLARE 
    @rowsHtml NVARCHAR(MAX), @htmlASxml XML;

;WITH CTE AS (
    SELECT 
        DENSE_RANK() OVER (ORDER BY p.PayTypeDesc) AS PayTypeDesc_GroupSortingIndex,
        ROW_NUMBER() OVER (PARTITION BY p.PayTypeDesc ORDER BY p.sort1, p.sort2) AS PayTypeDesc_GroupInnerSortingIndex,
        COUNT(*) OVER (PARTITION BY p.PayTypeDesc) AS PayTypeDesc_Count,
        ISNULL(p.PayTypeDesc,'') AS PayTypeDesc,
        ISNULL(p.PayDesc,'') AS PayDesc,
        ISNULL(p.PayFrequency,'')  AS PayFrequency,
        ISNULL(p.Currency,'') AS Currency,
        ISNULL(CAST(p.PerMonth AS VARCHAR(10)),'') AS PerMonth,
        ISNULL(CAST(p.PerAnnum AS VARCHAR(10)),'') AS PerAnnum
    FROM #saltmp p
)
SELECT @htmlASxml = (
    SELECT
        PayTypeDesc_Count AS 'PayTypeDesc/@rowspan',
        PayTypeDesc,
        PayDesc,
        PayFrequency,
        Currency,
        PerMonth,
        PerAnnum
    FROM (
        SELECT 
            PayTypeDesc_Count,
            PayTypeDesc,
            PayDesc,
            PayFrequency,
            Currency,
            PerMonth,
            PerAnnum,
            PayTypeDesc_GroupSortingIndex,
            PayTypeDesc_GroupInnerSortingIndex
        FROM CTE
        WHERE PayTypeDesc_GroupInnerSortingIndex = 1
        UNION ALL
        SELECT 
            null,null,
            PayDesc,
            PayFrequency,
            Currency,
            PerMonth,
            PerAnnum,
            PayTypeDesc_GroupSortingIndex,
            PayTypeDesc_GroupInnerSortingIndex
        FROM CTE
        WHERE PayTypeDesc_GroupInnerSortingIndex != 1
    ) as D 
    ORDER BY PayTypeDesc_GroupSortingIndex, PayTypeDesc_GroupInnerSortingIndex
    FOR XML PATH('tr')
);

Here is a screenshot of the replacements you should do. (I don't know why but some of my single-quoted strings are not showing up when I post the answer)

Plеase, don't forget to put your vote and accept this answer if appropriate. Thanks!

赞(0）分享  回复(0）举报  2023-10-15
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com