Accumulate dataset in varchar variable with ORDER BY clause

Question

I'm writing a QA procedure that notes records in the database that violate certain business rules, and sends an email to users informing them that they have some cleanup to do. Our business requirements include not "spamming" our users, so I must accumulate all the violations into one email before sending it.

This particular QA identifies duplicate WPGC.

-- variables already declared
DECLARE @th VARCHAR(MAX) SET @th = '';
DECLARE @tb VARCHAR(MAX) SET @tb = '';
DECLARE @tr VARCHAR(MAX) SET @tr = '';
DECLARE @tf VARCHAR(MAX) SET @tf = '';
-- yes the URL is munged. org_name is probably not a valid CRM organization.
DECLARE @url VARCHAR(MAX) SET @url = 'https://org_name.crm.dynamics.com/main.aspx?etc=@etc&id={@guid}&pagetype=entityrecord';

DECLARE @rc INT SET @rc = 0 ;

--------------------------------------------------------

    SELECT @th='<tr><th>Capyr</th><th>WPGC Name</th><th>Account Name</th></tr>'
, @tr='<tr><td>@3</td><td>@4</td><td><a href="@url">@5</a></td></tr>'
, @tb='', @tf='', @rc=0;

WITH d AS ( 
    SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@tr
    , '@url' , @url)
    , '@etc' , '10021')
    , '@guid', ISNULL(CAST(account      AS VARCHAR(50)),''))
    , '@3', isnull(CAST(campaignyear AS VARCHAR(50)),''))
    , '@4', isnull(CAST(name         AS VARCHAR(50)),''))
    , '@5', isnull(CAST(accountname  AS VARCHAR(50)),'')) d
    ,  1 n
    , campaignyear f1d, accountname f2a
FROM QA.dupeWPGC 
)
SELECT @tb += d, @rc += n 
FROM d
--ORDER BY f1d DESC, f2a ASC

If I run this query as written, the 170 rows show as expected.

Problem 1: The data is in no particular order. (2010, 2011, 2012, 2009, 2009, 2016, 2011...)
Solution 1: Add (uncomment) ORDER BY clause

Problem 2: If I run this query with the ORDER BY clause active (uncommented), only one record is included. (It does appear to be the last record of the correctly ORDERed dataset.)
Solution 2: ???

If I add the ORDER BY clause to the outermost query, only one row is returned.
If I add the ORDER BY clause anywhere else (such as in the view QA.dupeWPGC, in the CTE d, or in another CTE (e.g., , d2 AS ( SELECT * FROM d ORDER BY f1d DESC, f2a ASC)) then the final result remains unsorted (i.e., no difference is seen).

How can I cause this dataset to render a) completely, and b) properly sorted?

Finally, this is running on:

SELECT @@VERSION;
--------------------------------------------------------------------------------------------------
Microsoft SQL Server 2014 (SP2-GDR) (KB3194714) - 12.0.5203.0 (X64) 
    Sep 23 2016 18:13:56 
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

Answer 1

For Solution 1:

Update: Solution 1 does not work

You can try adding OPTION( MAXDOP 1 ) at the end of your ORDER BY:

ORDER BY f1d DESC, f2a ASC
OPTION( MAXDOP 1 )

I am not sure but my guess is that ORDER BY causes a parallel plan, which may cause issues.

I propose Solution 3:

It uses SQL Server built in FOR XML functionality

CREATE TABLE #Data( ID INT, campaignyear VARCHAR( 20 ), name VARCHAR( 20 ), account VARCHAR( 20 ), accountname VARCHAR( 20 ))
INSERT INTO #Data
SELECT 4, '2017', 'John', '123456', 'AccountName1' UNION ALL
SELECT 7, '2017', 'Paul', '654321', 'AccountName2' UNION ALL
SELECT 3, '2016', 'Jack', '123', 'AccountName3'

DECLARE @tb XML
SELECT @tb = (
SELECT 
    ( SELECT ISNULL( CONVERT( NVARCHAR( 50 ), campaignyear ), '' ) FOR XML PATH( 'td' ), TYPE ),
    ( SELECT ISNULL( CONVERT( NVARCHAR( 50 ), name ), '' ) FOR XML PATH( 'td' ), TYPE ),
    ( SELECT
        ( SELECT CONVERT( NVARCHAR( 255 ),
                N'https://org_name.crm.dynamics.com/main.aspx?etc=10021&id={' + ISNULL( CONVERT( NVARCHAR( 50 ), account ), '' ) + '}&pagetype=entityrecord' ) AS [@href],
            ISNULL( CONVERT( NVARCHAR( 50 ), accountname ), '' )
        FOR XML PATH( 'a' ), TYPE )
     FOR XML PATH( 'td' ), TYPE )
FROM #Data
ORDER BY ID -- !!!!!!!!!!! Note ORDER BY here
FOR XML PATH( 'tr' )
)
SELECT @tb

Output:

<tr>
  <td>2016</td>
  <td>Jack</td>
  <td>
    <a href="https://org_name.crm.dynamics.com/main.aspx?etc=10021&amp;id={123}&amp;pagetype=entityrecord">AccountName3</a>
  </td>
</tr>
<tr>
  <td>2017</td>
  <td>John</td>
  <td>
    <a href="https://org_name.crm.dynamics.com/main.aspx?etc=10021&amp;id={123456}&amp;pagetype=entityrecord">AccountName1</a>
  </td>
</tr>
<tr>
  <td>2017</td>
  <td>Paul</td>
  <td>
    <a href="https://org_name.crm.dynamics.com/main.aspx?etc=10021&amp;id={654321}&amp;pagetype=entityrecord">AccountName2</a>
  </td>
</tr>