how to order moth & year in sql pivote table

Hiteshkumar Patel 21 Reputation points
2021-02-22T11:53:57.64+00:00

Hi Everyone,
below is my code

	DECLARE @cols NVARCHAR (MAX)  
  
SELECT @cols = COALESCE (@cols + ',[' + sMonths + ']',   
               '[' + sMonths + ']')  
               FROM    (SELECT DISTINCT sMonths FROM tbl_TempSalesRepSales) PV   
			   ORDER BY sMonths   

result:
[Aug20],[Dec20],[Feb21],[Jan21],[Nov20],[Oct20],[Sep20]

i want in order bleow
[Aug20],[Sep20],,[Oct20],[Nov20][Dec20],[Jan21],[Feb21]

Thanks & Regards
Hitesh Patel

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,746 questions
0 comments No comments
{count} votes

Accepted answer
  1. Olaf Helper 28,781 Reputation points
    2021-02-22T13:12:29.773+00:00

    Then sort by them, like

    DECLARE @cols NVARCHAR (MAX)
    
    SELECT @cols = COALESCE (@cols + ',[' + sMonths + ']',  '[' + sMonths + ']')
    FROM    (SELECT DISTINCT sMonths, sYearN, sMonthN FROM tbl_TempSalesRepSales) AS PV 
    ORDER BY PV.sYearN, PV.sMonthN 
    

1 additional answer

Sort by: Most helpful
  1. Olaf Helper 28,781 Reputation points
    2021-02-22T12:28:51.5+00:00

    The result/column sMonths is a string and gets sorted alphanumeric;
    Do you have and additional information in the table related to a date/mont, so that you could sort by that?