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: