CODEWITHSUNDEEP
sqlsql-order-bypivot
Calgar99
Calgar99

Reputation: 1688

MS SQL:Pivot Table order columns

I'm hoping some can help clarify what I should be doing. I'm essentially reading in a Table with dates and then Im trying to convert these dates into individual columns.

Unfortunately the columns come out out of order. How do I order these columns into |01/01/2013|02/01/2013| etc? Any guidance would be much appreciated.

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.GL_Effective_Date) 
            FROM [03_rdm].[Table_2013] c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT Ref_Account_Class, ' + @cols + ' from 
            (
                select TOP 100 PERCENT
                    Ref_Account_Class
                    , GL_Amount
                    , GL_Effective_Date
                from [03_rdm].[Table_2013]
                where Ref_Account_Class = ''Accounts Receivable''
                order by  GL_Effective_Date
           ) x
            pivot 
            (
                 max(GL_Amount)
                for GL_Effective_Date in (' + @cols + ')
            ) p '
execute(@query)

Upvotes: 1

Views: 527

Answers (2)

GarethD
GarethD

Reputation: 69789

You need to order in this statement:

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.GL_Effective_Date) 
        FROM [03_rdm].[Table_2013] c
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

When using distinct though, you can only order by items in the select list, so you can't order by c.GL_Effective_Date, it would need to be the full statement (',' + QUOTENAME(c.GL_Effective_Date), but since QUOTENAME implicitly converts your date to a VARCHAR these will not come out in order. So instead of DISTINCT you can use GROUP BY to remove duplicates:

SET @cols = STUFF((SELECT ',' + QUOTENAME(c.GL_Effective_Date) 
        FROM [03_rdm].[Table_2013] c
        GROUP BY c.GL_Effective_Date
        ORDER BY c.GL_Effective_Date
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

Which should give the ordering you require. This should also be marginally more efficient, although probably not noticably. When you use a scalar function with distinct the function is applied to all rows, then duplicates removed from the results, however with group by, first duplicates are removed, then the function is applied to the results. As mentioned, this will not be noticable with QUOTENAME, but be a useful bit of knowledge to have for future reference.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270583

Instead of using distinct, use group by:

SET @cols = STUFF((SELECT ',' + QUOTENAME(c.GL_Effective_Date) 
                   FROM [03_rdm].[Table_2013] c
                   GROUP BY c.GL_Effective_Date
                   ORDER BY c.GL_Effective_Date
                   FOR XML PATH(''), TYPE
                  ).value('.', 'NVARCHAR(MAX)'),
                 1, 1, '');

Upvotes: 0

Related Questions