Execute the following SQL Server T-SQL script in Management Studio Query Editor to demonstrate a data-driven ORDER BY columns.

-- SQL order by case - SQL case order by - SQL conditional sort - dynamic sorting

USE AdventureWorks

GO

DECLARE  @SortBy          CHAR(10),

         @SortByDirection CHAR(1)

SET @SortBy = 'City'

SET @SortByDirection = 'A' -- or D for descending

SELECT   AddressLine1,

         City,

         State = p.Name,

         PostalCode

FROM     Person.Address a

         INNER JOIN Person.StateProvince p

           ON a.StateProvinceID = p.StateProvinceID

WHERE    AddressLine1 LIKE '%drive%'

ORDER BY CASE

           WHEN @SortBy = 'Postal'

                AND @SortByDirection = 'D' THEN PostalCode

         END DESC,

         CASE

           WHEN @SortBy = 'Postal'

                AND @SortByDirection = 'A' THEN PostalCode

         END,

         CASE

           WHEN @SortBy = 'State'

                AND @SortByDirection = 'D' THEN p.[Name]

         END DESC,

         CASE

           WHEN @SortBy = 'State'

                AND @SortByDirection = 'A' THEN p.[Name]

         END,

         CASE

           WHEN @SortBy = 'City'

                AND @SortByDirection = 'D' THEN City

         END DESC,

         CASE

           WHEN @SortBy = 'City'

                AND @SortByDirection = 'A' THEN City

         END

GO

/* Partial results

AddressLine1            City        State       PostalCode

42500 West Park Drive   Atlanta     Georgia     30308

4167 Whitehall Drive    Ballard     Washington  98107

4304 Dos Rios Drive     Ballard     Washington  98107

4930 Augustine Drive    Ballard     Washington  98107

5793 St. Helena Drive   Ballard     Washington  98107

630 Plymouth Drive      Ballard     Washington  98107

1727 The Trees Drive    Ballard     Washington  98107

*/