How to make conditional ordering for two or more columns

Question

In MS SQL Server 2005 I am writing one query with conditional sort and my problem is that I do not know how can I sort conditional using two columns?

If I wrote code like this it is working normaly

select
 *
from 
 table
order by 
 case @pkr 
 when 'kol' then kol
 when 'nci' then nci
 end

I do not know how to make conditional ordering for two or more columns

select
 *
from 
 table
order by 
 case @pkr
 when 'KOL-NCI' then kol,nci
 when 'kol-MPCI' then kol,mpci
 end

There is an idea to make dynamic TSQL and use sp_executesql but I am still looking for a better idea?

• gbn: possible duplicate of 'Conversion failed' error with ORDER BY CASE expression
• joanolo: You can also check Does it make sense to have CASE .. END in an ORDER BY?. Although that question was posed in the context of PostgreSQL, most of the comments and considerations WRT dynamic query vs CASE can be applicable to this case.

Answer 1

I'll admit I've never had to do this before so there was a bit of head scratching involved. Simple example table to demonstrate:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]') AND type in (N'U'))
 DROP TABLE [dbo].[MyTable]
GO
CREATE TABLE dbo.MyTable
(
 col1 INT
 , col2 CHAR(1)
)
GO
INSERT dbo.MyTable (col1, col2) VALUES (1, 'A')
INSERT dbo.MyTable (col1, col2) VALUES (1, 'B')
INSERT dbo.MyTable (col1, col2) VALUES (1, 'C')
INSERT dbo.MyTable (col1, col2) VALUES (2, 'A')
INSERT dbo.MyTable (col1, col2) VALUES (2, 'B')
INSERT dbo.MyTable (col1, col2) VALUES (2, 'C')
INSERT dbo.MyTable (col1, col2) VALUES (3, 'A')
INSERT dbo.MyTable (col1, col2) VALUES (3, 'B')
INSERT dbo.MyTable (col1, col2) VALUES (3, 'C')

Using an @SortStyle parameter to differentiate between sort orders, @SortStyle =1 will sort by col1 ASC, col2 DESC and @SortStyle=2 sort by col2 DESC, col1 ASC.

DECLARE @SortStyle INT
SET @SortStyle = 1
SELECT
 col1
 , col2
FROM
 dbo.MyTable
ORDER BY
 CASE
 WHEN @SortStyle = 1 THEN col1
 END ASC,
 CASE
 WHEN @SortStyle = 1 THEN col2
 END DESC,
 CASE
 WHEN @SortStyle = 2 THEN col2
 END DESC,
 CASE
 WHEN @SortStyle = 2 THEN col1
 END ASC
SET @SortStyle = 2
SELECT
 col1
 , col2
FROM
 dbo.MyTable
ORDER BY
 CASE
 WHEN @SortStyle = 1 THEN col1
 END ASC,
 CASE
 WHEN @SortStyle = 1 THEN col2
 END DESC,
 CASE
 WHEN @SortStyle = 2 THEN col2
 END DESC,
 CASE
 WHEN @SortStyle = 2 THEN col1
 END ASC

How do you ORDER BY a parameter covers the simpler case of sorting by just 1 column.

Answer 2

Assuming you have more cases (I added one), and all types are compatible,

order by 
 case @pkr
 when 'KOL-NCI' then kol
 when 'kol-MPCI' then kol
 when 'foo-bar' then foo
 end,
 case @pkr
 when 'KOL-NCI' then nci
 when 'kol-MPCI' then mpci
 when 'foo-bar' then bar 
 end

It isn't a multi-column sort: you have a primary sort, followed by a secondary sort. Just look at the sort dialogue box in Excel to see what I mean.

Answer 3

With the example you give it is simple:

select *
from table
order by kol, case @pkr
 when 'KOL-NCI' then nci
 when 'kol-MPCI' then mpci
 end

There is an idea to make dynamic TSQL and use sp_executesql but I am still loking for better idea.

It is always nice to avoid dynamic SQL where possible

Answer 4

There is a simpler way, and that doesn't need a procedure, or several "cases" Just use array

Ex:

select
 col1, col2, col3, col4
from 
 table
order by 
 case @parameter
 when '1' then array[col1,col3,col4]
 when '2' then array[col1]
 when '3' then array[col4,col2]
 end

tested in postgresql

• Michael Green: The question is specifically for SQL Server.