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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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