Search code examples
sqlsql-server-2012

CASE Statement for Order By Clause with Multiple Columns and Desc/Asc Sort

Following on from my earlier question here Case statement for Order By clause with Desc/Asc sort I have a statement like this:

SELECT 
    *
FROM
    TableName
WHERE
ORDER BY 
    CASE @OrderByColumn WHEN 1 THEN Forename END DESC, 
    CASE @OrderByColumn WHEN 2 THEN Surname END ASC

This works well, but sometimes I need more than column in the order by. I actually need something like this:

.....
ORDER BY
    CASE @OrderByColumn WHEN 1 THEN Forename, Date, Location END DESC

I can't work out how to make the CASE statement allow multiple columns in the THEN part.

Solution

  • Do you need this?

    ORDER BY
    CASE @OrderByColumn WHEN 1 THEN Forename END DESC, Date, Location,
    CASE @OrderByColumn WHEN 2 THEN Surname END ASC