Dynamic Order By With Multiple Fields

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamic Order By With Multiple Fields

Author

Topic

attenberger@xitech.com
Starting Member

2 Posts

Posted - 2001-11-26 : 13:21:37

We are trying to create a dynamic "order by" with multiple fields. A Case statement works with one field, but if we add a second, we get a syntax error. There is a problem with the comma. The sample code is from the Northwind Traders db.

CASE Products.CategoryID
WHEN 1 THEN Suppliers.CompanyName
WHEN 2 THEN Products.ProductName
ELSE Products.ProductName
END

..This works fine, but when we go to add a second column, we get the syntax error.

CASE Products.CategoryID
WHEN 1 THEN Suppliers.CompanyName, Products.ProductName
WHEN 2 THEN Products.ProductName, Products.UnitPrice
ELSE Products.ProductName, Products.UnitsinStock
END

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2001-11-26 : 13:25:21

CASE Products.CategoryID
WHEN 1 THEN Suppliers.CompanyName
WHEN 2 THEN Products.ProductName
ELSE Products.ProductName
END,
CASE Products.CategoryID
WHEN 1 THEN Products.ProductName
WHEN 2 THEN Products.UnitPrice
ELSE Products.UnitsinStock
END

Ed: Looking at the names I get the feeling that there might be some data type mismatch so you may need to add a cast.

Edited by - LarsG on 11/26/2001 13:28:17

attenberger@xitech.com
Starting Member

2 Posts

Posted - 2001-11-26 : 14:25:13

Thanks, that worked!! Here's our whole SQL statement against the NorthWind Trader's database.

We didn't have to cast any data types. Our second filter on category 1 was actually a sum (not the ProductName as I put in the original post)...so I'm guessing that's why we didn't have to cast.

SELECT Products.CategoryID,
Products.ProductID, Products.ProductName, Suppliers.CompanyName,
SUM([Order Details].Quantity) AS TotalQuantitySold,
Products.UnitPrice,Products.UnitsinStock

FROM Products
LEFT JOIN Categories ON Products.CategoryID = Categories.CategoryID
LEFT JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID
LEFT JOIN [Order Details] ON [Order Details].ProductID = Products.ProductID

GROUP BY
Products.CategoryID, Products.ProductName,Suppliers.CompanyName, Products.ProductID, Products.UnitPrice,
Products.UnitsInStock

ORDER BY
Products.CategoryID,

CASE Products.CategoryID
WHEN 1 THEN Suppliers.CompanyName
WHEN 2 THEN Suppliers.CompanyName
ELSE Suppliers.CompanyName
END,

CASE Products.CategoryID
WHEN 1 THEN SUM([Order Details].Quantity)
WHEN 2 THEN Products.UnitPrice
ELSE Products.UnitsInStock
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources