XML Path in ordering number SQL Server

Question

I would like to get full range of date between two different temporary table for pivot table. The query is like this :

DECLARE @cols AS NVARCHAR(MAX)
DECLARE @colswithNoNulls AS NVARCHAR(MAX)
DECLARE @query  AS NVARCHAR(MAX)
DECLARE @tanggal_awal DATE
DECLARE @tanggal_akhir DATE
DECLARE @print NVARCHAR(MAX)
DECLARE @querycount AS NVARCHAR(MAX)

CREATE TABLE #datatable  
(
    product_id int,
    product_date date,
    product_ammount int
)

SET @tanggal_awal = convert(DATE,'02-01-2017')
SET @tanggal_akhir = convert(DATE,DATEADD(dd,-1,(DATEADD(mm,1,@tanggal_awal))))

INSERT INTO #datatable (product_id,product_date,product_ammount) 
VALUES (1, GETDATE(), 100), (1, GETDATE(), 900),
       (2, DATEADD(DD, -1, GETDATE()), 400),
       (3, DATEADD(DD, 4, GETDATE()), 300),
       (1, DATEADD(DD, 4, GETDATE()), 200),
       (2, DATEADD(DD, 2, GETDATE()), 700),
       (4, DATEADD(DD, -3, GETDATE()), 1000),
       (4, DATEADD(MM, 1, GETDATE()), 200)

;WITH CTE (datelist,maxdate) AS 
(
    SELECT 
        CONVERT(INT, (MIN(DATEPART(day, @tanggal_awal)))) datelist,   
        CONVERT(INT, MAX(DATEPART(day, product_date))) maxdate
    FROM 
        #datatable

    UNION ALL

    SELECT 
        CONVERT(INT, (DATEPART(day, datelist))), 
        CONVERT(INT, (DATEPART(day, @tanggal_akhir)))
    FROM 
        cte
    WHERE 
        datelist < maxdate
) 
SELECT c.datelist 
INTO #temp
FROM cte c
ORDER BY c.datelist
OPTION (maxrecursion 0)

SELECT * FROM #temp

SELECT 
    @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(CONVERT(int, datelist)) 
                   FROM #temp
                   FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') 

PRINT @cols

However, the result is not like i am expecting. Based on the print result, it shows something like this:

[1],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[2],[20],[21],[22],[23],[24],[25],[26],[27],[28],[3],[4],[5],[6],[7],[8],[9]

The result I want is something like this

 [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28] 

What should I do to solve this query? Thank you very much :)

Answer 1

Remove the DISTINCT keyword and add an ORDER BY clause to the SELECT statement:

SELECT @cols =  STUFF((SELECT ',' + QUOTENAME(CONVERT(int, datelist)) 
                FROM #temp
                ORDER BY CONVERT(int, datelist)
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
                ,1,1,''
                )