locked
How to sort by month in pivot table RRS feed

  • Question

  • User-1640542475 posted

    Hi

    I have an issue that I could not sort/order by MonthNo in Pivot

    Here is my Code

    SELECT * FROM (SELECT CurrCode,YEARNO,Monthno,TOTALAMOUNT
    			FROM [dbo].[ML_tbl_Analaysis] where Currcode =@CURRCODE) tbl 
    			PIVOT (sum(TOTALAMOUNT) FOR [yearno] IN ([2016],[2017],[2018],[2019],                                    [2020],[2021],[2022],[2023],[2024],[2025])) 
    			as pivot_table 

    Please advice me

    Thank you

    maideen

    Friday, February 28, 2020 12:53 AM

Answers

  • User281315223 posted

    If I remember correctly, the inner query within a PIVOT doesn't support ordering, however since you are simply querying the results _from_ the PIVOT operation, you should be able to order by in your outer select statement:

    SELECT   * 
    FROM     (SELECT CurrCode,
                   YEARNO,
                   Monthno,
                   TOTALAMOUNT
    	 FROM   [dbo].[ML_tbl_Analaysis] 
             WHERE  Currcode =@CURRCODE) tbl 
    	 PIVOT  (sum(TOTALAMOUNT) FOR [yearno] IN ([2016],[2017],[2018],[2019], [2020],[2021],[2022],[2023],[2024],[2025])) AS pivot_table
    ORDER BY Monthno

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 28, 2020 6:10 AM