DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to apply CASE in ORDER BY for dynamic sort?

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:

Geography Country Month SalesStaff Date Category Subcat Product Color Size Qty TotalDollar OrderNo
Europe France 2003-01 Ranjit Varkey Chudukatil 1/1/2003 Bikes Mountain Bikes Mountain-200 Black, 38 Black 38 4 $4,917.84 SO48755
Europe France 2003-01 Ranjit Varkey Chudukatil 1/1/2003 Bikes Mountain Bikes Mountain-200 Black, 38 Black 38 2 $2,458.92 SO48774
Europe France 2003-01 Ranjit Varkey Chudukatil 1/1/2003 Bikes Mountain Bikes Mountain-200 Black, 42 Black 42 1 $1,229.46 SO48774
Europe France 2003-01 Ranjit Varkey Chudukatil 1/1/2003 Bikes Mountain Bikes Mountain-200 Black, 42 Black 42 5 $6,147.29 SO48755

 

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE