how to change the SQL script so that the columns with the amount are right-aligned

user_13045023 0
Viewed 137

Please help me change the request so that the columns with the amount are right-aligned? They are tagged AS 'money'. I will be using dynamic SQL, so the fields will change. It is important for me that whatever field is not marked with the money tag, is shifted to the right and everything else is centered. I am using this script. Below is the result that I expect.

  insert into #a 
  Values (1, 'GB', 2000),
         (2, 'DE', 170),
         (3, 'PT', 960)


    DECLARE @Values             NVARCHAR(MAX)

    select @Values = CAST((SELECT ROW_NUMBER() OVER(ORDER BY UserID)  AS 'td','',
                        (SELECT CONCAT('https://backend.com/User/UserDetails.aspx?UserID=',UserId) AS [@href],UserID FOR XML PATH('a'),TYPE) AS 'td',''
                       ,Country AS 'td',''
                       ,TotalDeposits AS 'money',''
    FROM  #a
    FOR XML PATH('tr'),ELEMENTS ) AS NVARCHAR(MAX))
    
    select '<html><body><table border=1 cellpadding=10 style="border-collapse:collapse;">' +  + @Values + '</table></body></html>'


<html>
  <body>
    <table border=1 cellpadding=10 style="border-collapse:collapse;">
      <tr align="center" valign="center">
        <td>1</td>
        <td>
          <a href="https://backend.com/User/UserDetails.aspx?UserID=1">
            <UserID>1</UserID>
          </a>
        </td>
        <td>GB</td>
        <td style="text-align:right;vertical-align:middle;">2000</td>
      </tr>
      <tr align="center" valign="center">
        <td>2</td>
        <td>
          <a href="https://backend.com/User/UserDetails.aspx?UserID=2">
            <UserID>2</UserID>
          </a>
        </td>
        <td>DE</td>
        <td style="text-align:right;vertical-align:middle;">170</td>
      </tr>
      <tr align="center" valign="center">
        <td>3</td>
        <td>
          <a href="https://backend.com/User/UserDetails.aspx?UserID=3">
            <UserID>3</UserID>
          </a>
        </td>
        <td>PT</td>
        <td style="text-align:right;vertical-align:middle;">960</td>
      </tr>
    </table>
  </body>
</html>
xml sql-server
1 Answers

It is an easy task for XQuery.

Benefits:

  • No strings concatenation.
  • No worries for NULL values.
  • Very easy to create, very easy to maintain.
  • UI styling is controlled via CSS.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (UserID INT PRIMARY KEY, Country CHAR(2), TotalDeposits MONEY, IsMoney BIT);
INSERT INTO @tbl (UserID, Country, TotalDeposits, IsMoney) VALUES
(1, 'GB', 2000, 0),
(2, 'DE', 170, 1),
(3, 'PT', 960, 0)
-- DDL and sample data population, end

DECLARE @xhtmlBody XML
   , @body NVARCHAR(MAX)
   , @tableCaption VARCHAR(30) = 'Users list';

SET @xhtmlBody = (SELECT (
SELECT * FROM @tbl FOR XML PATH('row'), TYPE, ROOT('root'))
.query('<html><head>
            <meta charset="utf-8"/>
            (: including embedded CSS styling :)
            <style>
            table <![CDATA[ {border-collapse: collapse;  width: 300px;} ]]>
            th <![CDATA[ {background-color: #4CAF50; color: white;} ]]>
            th, td <![CDATA[ { text-align: left; padding: 8px;} ]]>
            tr:nth-child(even) <![CDATA[ {background-color: #f2f2f2;} ]]>
            #green <![CDATA[ {background-color: lightgreen;} ]]>
         </style>
         </head>
         <body>
<table border="1" cellpadding="10" style="border-collapse:collapse;">
   <caption><h2>{sql:variable("@tableCaption")}</h2></caption>
   <thead>
      <tr>
        <th>No.</th>
        <th>UserID</th>
        <th>Country</th>
        <th>TotalDeposits</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><a href="{concat(''https://backend.com/User/UserDetails.aspx?UserID='',data($row/UserID[1]))}">
                        <UserID>{data($row/UserID)}</UserID>
                    </a></td>
            <td>{data($row/Country)}</td>
            <td style="{concat(if (data($row/IsMoney="1")) then "text-align:right;"
                else "text-align:left;", "vertical-align:middle;")}">{data($row/TotalDeposits)}</td>
        </tr>
}
</tbody></table></body></html>'));

SELECT @xhtmlBody;

SET @body = CAST(@xhtmlBody AS NVARCHAR(MAX));

Output

<html>
  <head>
    <meta charset="utf-8" />
    <style>
            table  {border-collapse: collapse;  width: 300px;} 
            th  {background-color: #4CAF50; color: white;} 
            th, td  { text-align: left; padding: 8px;} 
            tr:nth-child(even)  {background-color: #f2f2f2;} 
            #green  {background-color: lightgreen;} 
         </style>
  </head>
  <body>
    <table border="1" cellpadding="10" style="border-collapse:collapse;">
      <caption>
        <h2>Users list</h2>
      </caption>
      <thead>
        <tr>
          <th>No.</th>
          <th>UserID</th>
          <th>Country</th>
          <th>TotalDeposits</th>
        </tr>
      </thead>
      <tbody>
        <tr align="center" valign="center">
          <td>1</td>
          <td>
            <a href="https://backend.com/User/UserDetails.aspx?UserID=1">
              <UserID>1</UserID>
            </a>
          </td>
          <td>GB</td>
          <td style="text-align:left;vertical-align:middle;">2000.0000</td>
        </tr>
        <tr align="center" valign="center">
          <td>2</td>
          <td>
            <a href="https://backend.com/User/UserDetails.aspx?UserID=2">
              <UserID>2</UserID>
            </a>
          </td>
          <td>DE</td>
          <td style="text-align:right;vertical-align:middle;">170.0000</td>
        </tr>
        <tr align="center" valign="center">
          <td>3</td>
          <td>
            <a href="https://backend.com/User/UserDetails.aspx?UserID=3">
              <UserID>3</UserID>
            </a>
          </td>
          <td>PT</td>
          <td style="text-align:left;vertical-align:middle;">960.0000</td>
        </tr>
      </tbody>
    </table>
  </body>
</html>
user_19323110
Related