Execute the following Microsoft SQL Server T-SQL script in SSMS Query Editor to demonstrate of the CASE conditional function in an ORDER BY clause for dynamic sorting.

USE AdventureWorks2008;

SELECT

   ST.[Group] AS [Geography], 

   ST.Name AS Country,

   CONVERT(CHAR(4),YEAR(SOH.OrderDate))+'-'+RIGHT('0'+

   CONVERT(VARCHAR,MONTH(SOH.OrderDate) ),2) AS [Month],

   P.FirstName +' '+P.LastName as SalesStaff,

   CONVERT(DATE,SOH.OrderDate) AS [Date],

   PC.Name AS Category,

   PSC.Name AS Subcat,

   PRD.Name AS Product,

   PRD.Color, PRD.Size,

   SUM(SOD.OrderQty) AS Qty,

   '$'+CONVERT(VARCHAR,CONVERT(money,SUM(SOD.LineTotal)),1) AS TotalDollar,

    SOH.SalesOrderNumber AS [OrderNo]

FROM Sales.SalesPerson AS SP

   INNER JOIN Sales.SalesOrderHeader AS SOH

      ON SP.BusinessEntityID = SOH.SalesPersonID

   INNER JOIN Person.Person AS P

      ON P.BusinessEntityID = SP.BusinessEntityID

   INNER JOIN Sales.SalesOrderDetail AS SOD

      ON SOD.SalesOrderID = SOH.SalesOrderID

   INNER JOIN Production.Product AS PRD

      ON SOD.ProductID = PRD.ProductID

   INNER JOIN Sales.SalesTerritory AS ST

      ON ST.TerritoryID = SP.TerritoryID

   INNER JOIN Production.ProductSubcategory AS PSC

      ON PRD.ProductSubcategoryID = PSC.ProductSubcategoryID

   INNER JOIN Production.ProductCategory AS PC

      ON PC.ProductCategoryID = PSC.ProductCategoryID

GROUP BY PC.Name, SOH.OrderDate, SOH.SalesOrderNumber,

   PSC.Name, PRD.Name, SOH.SalesPersonID,

   P.FirstName+' '+P.LastName,

   ST.[Group], ST.CountryRegionCode,

   ST.Name, PRD.Color, PRD.Size

HAVING (YEAR(SOH.OrderDate) NOT IN ('2001','2002')

   AND ST.[Group] != 'North America' AND PC.Name='Bikes' )

ORDER BY [Geography], Country, [Month], Category, Subcat, Product,

      CASE

      WHEN PRD.Size = 'S' THEN 'A'

      WHEN PRD.Size = 'M' THEN 'B'

      WHEN PRD.Size = 'L' THEN 'C'

      WHEN PRD.Size = 'XL' THEN 'D'

      ELSE PRD.Size

      END

GO

Partial results: