locked
How to sort by month in pivot table RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    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

  • Question
    Sign in to vote
    0
    Sign in to vote
    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