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 :)

Serge · Accepted Answer · 2017-02-10 08:17:41Z

2

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,''
                )

answered Feb 10, 2017 at 8:17

Serge

4,0662 gold badges20 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Zohar Peled Over a year ago

This have the potential to produce 2 numbers that are equal. This is why in my answer (that's almost identical to yours) I've added a group by clause.

Faris Fajar Over a year ago

so why i shouldn't use distinct in this problem ? your answer works like a charm :) . can you explain to me the mechanic when we distinct in xml parh ?

Serge Over a year ago

SQL Server won't allow a DISTINCT in this case. If you try, you will get ORDER BY items must appear in the select list if SELECT DISTINCT is specified. error message. You need to guarantee that items in datelist don't repeat. Either add a GROUP BY, as suggested by @FarisFajar, or use DISTINCT when populating the #temp table: SELECT DISTINCT c.datelist INTO #temp

Zohar Peled · Accepted Answer · 2017-02-10 08:18:01Z

1

Here is one way to do it:

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

I've changed the DISTINCT to GROUP BY so that I could use CONVERT(int, datelist) in the ORDER BY clause.

answered Feb 10, 2017 at 8:18

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

Comments

Alfaiz Ahmed · Accepted Answer · 2017-02-10 08:19:58Z

 Getting TheOutput as
    [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]

      Begin TRAN

        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  ',' + QUOTENAME(CONVERT(nvarchar(20), datelist)) 
                        FROM #temp t1   
                         FOR XML PATH (''))
                  , 1, 1, '') from #temp t2


        SELECT @cols          


        PRINT @cols

        ROLLBACK TRAN

Collectives™ on Stack Overflow

XML Path in ordering number SQL Server

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related