Using Dynamic SQL To Order Column Names

Question

Post reply

Using Dynamic SQL To Order Column Names

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Ok, trying to bring this to the original topic.

This is what I was thinking as an approach.

Select Top(10) HotelName, Price, CustomerRating

From dbo.VacationDestinations

where Price <= @Price AND CustomerRating = @CustomerRating

Order By CASE WHEN @ratingWeight > @priceWeight THEN CustomerRating * -1 ELSE Price END,

CASE WHEN @ratingWeight > @priceWeight THEN Price ELSE CustomerRating * -1 END

However, I have a question.

If you're filtering by a specific Customer Rating, why do you need to order at all? In that case, it will always return the same results as this:

Select Top(10) HotelName, Price, CustomerRating

From dbo.VacationDestinations

where Price <= @Price AND CustomerRating = @CustomerRating

Order By Price

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 2

That will "only" handle the price and rating combination and only in that order. We're going to need some dynamic SQL here and it needs to be in the form of Gail Shaw's "Catch All" query.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLUSERMAN SSC Eights! Points: 804 More actions · Answer 3

Hi, thanks for replying. The columns I've shown in this post are all of the columns of my current table. I might add more columns to my table in the future. Also priceWeight and ratingweight are not table columns, their only use is to determine the order in which the columns should be listed in the Order By clause.

I tested what difference it would make if I list the columns in different orders in the Order By clause and sure enough the order in which the results came back are different depending on the order of the columns.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

The only columns mentioned have been Price, Rating and Location. If rating is a single value, I'll assume that location would be as well (You won't be looking for hotels in Atlanta and New York at the same time). Also, location ordered by description wouldn't be very useful. An order by distance from certain point should be better. Of course, those are just assumptions.

With more information, I'd agree that the catch-all query with dynamic SQL would be the best option. Meanwhile, it depends.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

alan.cdes SSC Enthusiast Points: 106 More actions · Answer 5

Try to insert your colum name with rates on a temporary table or variable table.

Order by rate.

Output the ordered colum name on comma delimited list.

Insert the comma delimited list in the order by clause on a dynamic sql.

Execute the dynamic sql

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Do you mean like this?

Select Top(10) HotelName, Price, CustomerRating

From dbo.VacationDestinations

where Price <= @Price AND CustomerRating = @CustomerRating

Order By

CASE WHEN @ratingWeight >= @priceWeight THEN CustomerRating ELSE Price END DESC,

CASE WHEN @ratingWeight >= @priceWeight THEN Price ELSE CustomerRating END DESC

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 7

alan.cdes (10/30/2015)
Try to insert your colum name with rates on a temporary table or variable table.
Order by rate.
Output the ordered colum name on comma delimited list.
Insert the comma delimited list in the order by clause on a dynamic sql.
Execute the dynamic sql

Or create TVF which builds character string to odrer by from rates and values .