Non-Aggregate PIVOT can't sort

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Non-Aggregate PIVOT can't sort

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2014-04-16 : 11:39:21

so i have a query that gives me my desired output except the fact that my column data i can't get to sort. this paticular query i am placing in the dataset for a Matrix SSRS report. i can't get this to sort either. i mean i can export the report to Excel and then manually sort, but this report will be going out to clerks and don't want them to have to do any manual processing. here is my sql and if anyone knows how to get the RowNum to sort then it would be greatly appreciated. it seems that i have to work around a work around and it seems pretty crazy for something that seems so simple. wrong.

SELECT *
FROM(
SELECT
LocationMovedTo, CONVERT(NVARCHAR,NAVLocation) AS NAVLocation,
CASE WHEN EquipType='SWAB RIG' THEN 'S'+RigNumber ELSE RigNumber END AS RigNumber, ROW_NUMBER() OVER (PARTITION BY LocationMovedTo ORDER BY LocationMovedTo) AS RowNum
FROM(
SELECT
CASE
WHEN MiscNumField1=1 THEN SUBSTRING(RigNumber,5,4)+'- WS'
WHEN MiscNumField1>1 THEN SUBSTRING(RigNumber,5,4)+'- OOS'
ELSE SUBSTRING(RigNumber,5,4) END AS RigNumber,
LocationMovedTo, NAVLocation, MiscNumField1 AS StatusCode, MoveType, MiscTextField2 AS StatusDescription, MiscTextField3 AS NAVLocal2, EquipType
FROM VW_WellService_AssetTrackCurrentLocation
WHERE LocationMovedTo NOT IN ('MERADM','MERELC') AND SUBSTRING(RigNumber,1,3)='RIG'
) AS X
) AS Y
PIVOT (
max(RigNumber) FOR RowNum in ([1],[2],[3],[4],[5],[6],[7],

,[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],
[31],[32],[33],[34],[35],[36],[37],[38],[39],[40])) as PVT

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-16 : 11:49:28

I don't see any attempt in your code to sort - can't you just add an ORDER BY clause at the end?
And if you mean the column order then you just need to explicitly SELECT the columns in your desired order rather than use "*".

Be One with the Optimizer
TG

osupratt
Posting Yak Master

238 Posts

Posted - 2014-04-16 : 12:20:19

Well the data i need or want to sort is the RowNum which is the IN or [1], [2], [3], [4], etc. Not sure on how i can sort this. The LocationMovedTo field i can sort but don't need to because when placed in the matrix report it set the Column grouping as LocationMovedTo with a default sorting by LocationMovedTo.

osupratt
Posting Yak Master

238 Posts

Posted - 2014-04-16 : 14:12:56

OK. I added the field 'RigNumber' to the ORDER BY in the ROW_NUM statement and it is sorting correctly now. sorry as that was a mistake that i couldn't see.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources