Order by column month names

Question

0.00/5 (No votes)

See more:

Hi All ,

I have a query that selects all month and the corresponding values in each month . I want to sort the month order based on the date column . Could anyone help me please . Tried different ways but couldn't find a solution . Any help will be really appreciated .

For example :
My output right now :

Leave Jan Feb March Apr May Jun Jul Aug Sep Oct Nov Dec

Annual 0 0 2 2 2 0 0 0 0 0 0 0
Sick 0 0 2 2 2 0 0 0 0 0 0 0

The output I wanted :

Leave March Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb
Annual 2 2 2 0 0 0 0 0 0 0 0 0
Sick 2 2 2 2 2 0 0 0 0 0 0 0

ie the month column order should be based on mte.CalendarDate from the subquery .

What I have tried:

SELECT
		Leave,
		ISNULL([1], 0) AS January,
		ISNULL([2], 0) AS February,
		ISNULL([3], 0) AS March,
		ISNULL([4], 0) AS April,
		ISNULL([5], 0) AS May,
		ISNULL([6], 0) AS June,
		ISNULL([7], 0) AS July,
		ISNULL([8], 0) AS August,
		ISNULL([9], 0) AS September,
		ISNULL([10], 0) AS October,
		ISNULL([11], 0) AS November,
		ISNULL([12], 0) AS December
	FROM
			(
				SELECT
					--YEAR(mte.CalendarDate) AS Years,
					MONTH(mte.CalendarDate) AS Months,
					--DATENAME(month, mte.CalendarDate) AS Calmonth,
					FLOOR(SUM(mte.LeaveDays))AS leaveCount
					,mte.LeaveTypeID
					,mtl.Leave
			
				FROM MST_TBL_Emp_Leaves mte
				LEFT JOIN PPMS_TBL_EmployeeMaster em ON em.Autoid = mte.EmpId
				LEFT JOIN MST_TBL_Leave_Types mtl ON mtl.Autoid = mte.LeaveTypeID
				WHERE
					mte.LeaveTypeID IN (1, 2, 4, 8) AND
					mte.EmpId = 170 AND
					mte.CalendarDate BETWEEN '2023-02-27' AND GETDATE()
				GROUP BY
					--YEAR(mte.CalendarDate),
					MONTH(mte.CalendarDate)
					,mte.LeaveTypeID
					,mtl.Leave
					
			
			) AS source
		PIVOT
		(
			SUM(leaveCount)
			FOR Months IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]) 
		) AS pivotedTable

Posted 1-Jun-23 20:58pm

Member 12926744

Updated 6-Jun-23 2:44am

Add a Solution

Comments

Richard Deeming 2-Jun-23 3:33am

You want the column order to be based on some data we can't see, using some rules you haven't described.

Click the green "Improve question" link and update your question to include the sample data that you want to produce the expected output, and an explanation of why the columns should be ordered in that specific way.

If possible, create a small SQL Fiddle[^] to demonstrate the problem.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2023-06-06T02:44:00

You have explicitly defined the column order with

SQL

SELECT
		Leave,
		ISNULL([1], 0) AS January,
		ISNULL([2], 0) AS February,
		ISNULL([3], 0) AS March,
		ISNULL([4], 0) AS April,
		ISNULL([5], 0) AS May,
		ISNULL([6], 0) AS June,
		ISNULL([7], 0) AS July,
		ISNULL([8], 0) AS August,
		ISNULL([9], 0) AS September,
		ISNULL([10], 0) AS October,
		ISNULL([11], 0) AS November,
		ISNULL([12], 0) AS December
	FROM

So in theory all you have to do is

SQL

SELECT
		Leave,
		ISNULL([3], 0) AS March,
		ISNULL([4], 0) AS April,
		ISNULL([5], 0) AS May,
		ISNULL([6], 0) AS June,
		ISNULL([7], 0) AS July,
		ISNULL([8], 0) AS August,
		ISNULL([9], 0) AS September,
		ISNULL([10], 0) AS October,
		ISNULL([11], 0) AS November,
		ISNULL([12], 0) AS December,
		ISNULL([1], 0) AS January,
		ISNULL([2], 0) AS February
	FROM

Note similarly, the order that the columns will be returned from your pivot is defined by

SQL

FOR Months IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])

But I suspect there is a bit more to it than that - are you asking that the order should be "start from the month defined in

SQL

mte.CalendarDate BETWEEN '2023-02-27' AND GETDATE()

(i.e. in this case March) and order the subsequent months through to the following March?

If that is the case then you may need to use a Dynamic SQL statement - I described a similar scenario in my article Processing Loops in SQL Server[^]