Sort distinct string values by the decimal value

Question

I have following query:

SELECT Main.DEALER_CODE
     , LEFT(Main.[fees], LEN(Main.[fees]) - 1) AS [fees]
  FROM
       (   SELECT DISTINCT vlp2.DEALER_CODE
                , (   SELECT DISTINCT CAST(FUNDING_FEE_AMOUNT AS VARCHAR(20)) + ';' AS [text()]
                        FROM dbo.valid_lease_programs AS vlp
                        JOIN dbo.lease_programs       AS lp ON lp.LEASE_PROGRAM = vlp.LEASE_PROGRAM
                       WHERE lp.LEASE_REMARKS IS NOT NULL AND vlp.DEALER_CODE = vlp2.DEALER_CODE
                       ORDER BY 1
                      FOR XML PATH('')) [fees]
             FROM dbo.valid_lease_programs AS vlp2) [Main];

FUNDING_FEE_AMOUNT is the decimal column, but when it is casted to the VARCHAR, when the ORDER BY is ordering it as a string, so 2 > 19. I would normally order by FUNDING_FEE_AMOUNT without casting, but in that case I can't do that because of the DISTINCT keyword and I can't remove casting as I will not be able to add ";" as separator.

So the question, how can I concatenate these values but have number-like order?

Answer 1

You can use data() instead of text(), it will give you spaces between concatenated values, then just replace spaces with ';', this permits to not to cast your decimal as varchar and will give correct order.

Here is an example:

declare @t table (col decimal(10, 2));
insert into @t values (100.05), (11.01), (2.33), (20), (1);

select replace((  select distinct col  as 'data()' from @t order by col for xml path('')  ), ' ', ';') as res