SQL Server – Sorting Pivot Results

order-bypivotsql server

I just want to ask how to sort the result of a PIVOT in SQL Server.

When I use ORDER BY there is an error:

"The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified."

I want to sort it with edtID columns. Here is my pivot code:

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX),
        @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10);
SET @cols = STUFF((SELECT ',' + QUOTENAME(m.segCode + ',' + @NewLineChar + f.ftpCode + ',' + @NewLineChar + f.ftpShortDescription)
        FROM immMarketSegment m
        CROSS JOIN immFinishingType f
        ORDER BY segCode, ftpCode ASC
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')
SET @query = N' SELECT  
            exfDescription  AS exfDesc,
            edtDescription  AS edtDesc,
            exfCode AS exfCODE,
            edtExteriorFinishCode   AS edtCODE,
            edtID   AS ID,
            ' + @cols + '
        FROM 
        (
            SELECT  
                exfDescription,
                edtDescription,
                exfCode,
                edtExteriorFinishCode,
                edtID,
                ftpShortDescription,
                segShortDescription
            FROM immMarketSegment
            INNER JOIN immExteriorFinish ON
                exfCode = segCode
            INNER JOIN immExteriorFinishDetails ON
                edtExteriorFinishCode = segCode
            CROSS JOIN immFinishingType
        ) x
        Pivot 
        (
            MAX(ftpShortDescription)
            for segShortDescription in (' + @cols + ')
        ) p'
EXEC (@query)

Here is the result:

enter image description here

Best Answer

Put it after the ) p...

    Pivot 
    (
        MAX(ftpShortDescription)
        for segShortDescription in (' + @cols + ')
    ) p ORDER BY ID'

(Maybe include a new line...?)

Related Question