sqltiger avatar image
sqltiger asked

How do you force columns into a particular order?

I am using SQL Server 2008 working on an existing query that does not sort columns correctly. The columns indicate years of service. Column headers are, and should be in the following order: ![alt text][1] They keep coming in in numerical order by the first number: ![alt text][2] These columns come from a case statement which uses the hire date. Then a count is done on the employee_ID by years of service. The column headers used to begin with a), b), c), d), e), f) but the user has been deleting these and asked that they be deleted from the report. I now believe the a,b,c, etc. was used for sorting. Does anyone know how I can force the columns into the order I need them to be in? Thank you very much! P.S. GROUP BY: the case statement, hire date, job title, and employee id. ORDER BY: the case statement, job title, and hire date [1]: /storage/temp/735-yos.png [2]: /storage/temp/736-yos+wrong.png
yos.png (2.1 KiB)
yos wrong.png (867 B)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you show the table structure and some sample data and some sample output?
0 Likes 0 ·

1 Answer

ThomasRushton avatar image
ThomasRushton answered
If you've deleted that A, B, C etc, then one thing you could do is add in an ORDER BY statement like this: ORDER BY CASE ColumnHeader WHEN '0-1' THEN 0 WHEN '1-2' THEN 1 WHEN '2-5' THEN 2 WHEN '5-10' THEN 3 WHEN '10-15' THEN 4 WHEN '15+' THEN 5 END You mention also the "hire date" - is that in your outermost SELECT statement? If so, then you can sort on that, even if you're not selecting it.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

FYI, I agree that you should use the hire date as the sort criteria, not a manual case statement sort, even if you don't display the hire date. Much better to just it as sort criteria, just include hire date as a new column anyway. Better yet, if you are using SSRS or another reporting tool leave the sorting to that, instead of adding it to your data layer.
1 Like 1 ·
Thank you!! I can't wait to try this out tomorrow. Will let you know how it goes.
0 Likes 0 ·
Sorry, but this didn't work for me. I ended up creating separate case statements which work great, then putting them in the ORDER BY. However, now I'm having trouble doing a SUM on each. I opened another question "Can you convert datetime to nvarchar inside a case stmt so that you can use sum?" I do appreciate your help on my order by problem though! Have a great day.
0 Likes 0 ·
In that case, it seems that I may have misunderstood your schema...
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.