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

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

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?

how to order moth & year in sql pivote table

Accepted answer

Olaf Helper 41,006 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
Please sign in to rate this answer.
Hiteshkumar Patel 21 Reputation points

2021-02-22T13:34:23.707+00:00

Thanks now Column is showing inline
but now my date is subtract each rows

SELECT @cols = COALESCE (@cols + ',[' + sMonths + ']', '[' + sMonths + ']') FROM (SELECT DISTINCT sMonths, sYearN, sMonthN FROM tbl_TempSalesRepSales) AS PV ORDER BY PV.sYearN, PV.sMonthN DECLARE @query NVARCHAR(MAX) SET @query = ' SELECT * FROM ( SELECT * FROM tbl_TempSalesRepSales ) x PIVOT ( SUM(Quantity) FOR sMonths IN (' + @cols + ') ) p ' EXEC SP_EXECUTESQL @query

Olaf Helper 41,006 Reputation points

2021-02-22T14:27:41.75+00:00

Not clear what you mean here?
Please post table design as DDL, some sample data as DML and the expected result.

Hiteshkumar Patel 21 Reputation points

2021-02-22T14:38:01.003+00:00

Thanks Its Working Nice!

Viorel 112.7K Reputation points

2021-02-22T14:48:32.657+00:00

Maybe you need this query:

set @query = ' SELECT * FROM ( SELECT Quantity, sMonths FROM tbl_TempSalesRepSales ) x PIVOT ( SUM(Quantity) FOR sMonths IN (' + @cols + ') ) p '

MelissaMa-MSFT 24,176 Reputation points

2021-02-23T02:55:27.677+00:00

Hi @Hiteshkumar Patel ,

Welcome to Microsoft Q&A!

Glad that you already got your expected answer.

It is recommended for you to post the DDL together with INSERT statements with sample data if you post a new question next time.

Please also remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

Best regards
Melissa
Sign in to comment

how to order moth & year in sql pivote table

1 additional answer