Here is a more readable version of the code:

DECLARE @tabSalary NVARCHAR(MAX) = N'<table cellpadding="5" style="color:#000066;border-collapse:collapse;font-family:Arial,sans-serif;width:100%;font-size: 10.0pt;" border="1">';
DECLARE @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
    ) AS D 
    UNION ALL
    SELECT 
        null,
        PayDesc,
        PayFrequency,
        Currency,
        PerMonth,
        PerAnnum,
        PayTypeDesc_GroupSortingIndex,
        PayTypeDesc_GroupInnerSortingIndex
    FROM CTE
    WHERE PayTypeDesc_GroupInnerSortingIndex != 1
)
ORDER BY PayTypeDesc_GroupSortingIndex, PayTypeDesc_GroupInnerSortingIndex
FOR XML PATH('tr');

SET @tabSalary = REPLACE(@tabSalary, '&lt;td&gt;', '&lt;td style="text-align:center;"&gt;');
SET @tabSalary = REPLACE(@tabSalary, '&lt;/td&gt;', '&lt;/td&gt;');

Note that I’ve also replaced the REPLACE function with two separate REPLACE statements to make the code more readable.

Last modified on 2023-12-02