0

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

1

Put it after the ) p...

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

(Maybe include a new line...?)

  • 1
    great it works! thanks sir @Rob Farley. I added the ORDER BY ID after p. thanks for your help sir – Glenn Nov 5 '18 at 5:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.