Dynamic Order By? 1

[gbaughma]

I have the following snippet....

ORDER BY 
CASE 
	WHEN @selSort = '1' THEN Analyte
	WHEN @selSort = '2' THEN (Location, CollectionDate, Analyte)
	WHEN @selSort = '3' THEN CollectionDate, Analyte
	WHEN @selSort = '4' THEN Location, SampleID, Analyte
	WHEN @selSort = '5' THEN Location, Analyte, CollectionDate
	END

... and it doesn't like the commas if I have to order by multiple columns. As you can see, I've tried parenthesis, quotes, etc... neither worked.

Any thoughts?

TIA!



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[SQLBill]

Try this: (not tested)

ORDER BY 
CASE 
    WHEN @selSort = '1' THEN ' Analyte'
    WHEN @selSort = '2' THEN ' Location, CollectionDate, Analyte'
    WHEN @selSort = '3' THEN ' CollectionDate, Analyte'
    WHEN @selSort = '4' THEN ' Location, SampleID, Analyte'
    WHEN @selSort = '5' THEN ' Location, Analyte, CollectionDate'
    END

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[gbaughma]

Nope... putting them in single quotes breaks the "Order" clause completely (it treats it as a literal string instead of fields).



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gmmastros]

You need to think of the case statement as a single column. Obviously you can order by multiple columns, but each column would need to use a separate case statement. Kinda like this...

ORDER BY
-- First Column
CASE
    WHEN @selSort = '1' THEN Analyte
    WHEN @selSort = '2' THEN Location
    WHEN @selSort = '3' THEN CollectionDate
    WHEN @selSort = '4' THEN Location
    WHEN @selSort = '5' THEN Location
    END,
-- Second Column
CASE
    WHEN @selSort = '1' THEN NULL
    WHEN @selSort = '2' THEN CollectionDate
    WHEN @selSort = '3' THEN Analyte
    WHEN @selSort = '4' THEN SampleID
    WHEN @selSort = '5' THEN Analyte
    END,
-- Third Column
CASE
    WHEN @selSort = '1' THEN NULL
    WHEN @selSort = '2' THEN Analyte
    WHEN @selSort = '3' THEN NULL
    WHEN @selSort = '4' THEN Analyte
    WHEN @selSort = '5' THEN CollectionDate
    END

Notice how I used NULL when we didn't really care about subsequent ordering (as shown in the sel sort = 1 situation).

Also.... you need to be super careful about mixing data types in a case expression. SQL Server will use data type precedence to automatically convert your data types for you. Basically, SQL Server will look at all the branches of execution for your case statement, determine which one has the higher order precedence, and then convert all the other ones to it.

For example (copy paste to a query window)

Declare @Date DateTime
Declare @Number Int
Declare @String VarChar(20)

Select @Date = '20100405',
       @Number = 7,
       @String = 'Anything'

Declare @SortSel Int

Set @SortSel = 3

Select Case When @SortSel = 1 Then @Date
            When @SortSel = 2 Then @Number
            When @SortSel = 3 Then @String
            End As MySortOrder

When you run the code above, you will get an error.

[red]Conversion failed when converting datetime from character string.[/red]

SQL Server is trying to convert the string to a DateTime data type and 'Anything' is not a valid date. In a case like this, you are better off converting everything to a string. After converting everything to a string, you should get your expected behavior, but you need to make sure that it's still sorting the way you want. This usually means that you need to convert dates to a yyyymmdd format and you need to zero pad your numbers.

Declare @Date DateTime
Declare @Number Int
Declare @String VarChar(20)

Select @Date = '20100405',
       @Number = 7,
       @String = 'Anything'

Declare @SortSel Int

Set @SortSel = 3

Select Case When @SortSel = 1 Then Convert(VarChar(10), @Date, 112)
            When @SortSel = 2 Then Right('0000000000' + Convert(VarChar(10), @Number), 10)
            When @SortSel = 3 Then @String
            End As MySortOrder

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gbaughma]

I ended up using something very similar to your 3rd block of code.... it looks like it's correct now.

Here's what I did:

CASE 
	WHEN @selSort = '1' THEN Analyte
	WHEN @selSort = '2' THEN CONVERT(char(100),Location) + CONVERT(char(100),CollectionDate) + CONVERT(Char(100), Analyte)
	WHEN @selSort = '3' THEN CONVERT(Char(100),CollectionDate) + CONVERT(char(100), Analyte)
	WHEN @selSort = '4' THEN CONVERT(char(100),Location) + CONVERT(char(100),SampleID)+CONVERT(char(100), Analyte)
	WHEN @selSort = '5' THEN CONVERT(char(100),Location)+CONVERT(char(100),Analyte) + CONVERT(char(100), CollectionDate)
	END

I just arbitrarily picked 100 characters... I could probably fine-tune that... but I wanted to test to make sure it worked at all.

And it did.


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[SQLBill]

Yeah, I tested my script and it failed...however this worked. I had to dynamically create the command.

DECLARE @selsort INT
DECLARE @sqlcmd VARCHAR(1000)

SET @selsort = 3

SET @sqlcmd = 'SELECT ......
ORDER BY ' + (SELECT CASE 
    WHEN @selSort = '1' THEN 'Analyte'
    WHEN @selSort = '2' THEN 'Location, CollectionDate, Analyte'
    WHEN @selSort = '3' THEN 'CollectionDate, Analyte'
    WHEN @selSort = '4' THEN 'Location, SampleID, Analyte'
    WHEN @selSort = '5' THEN 'Location, Analyte, CollectionDate'
    END)

PRINT @sqlcmd
--EXEC(@sqlcmd)

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[markros]

By strange coincidence there is a thread on this exact topic today in MSDN forum

http://social.msdn.microsoft.com/Fo...l/thread/f9960367-5de5-4ceb-ad01-9ff1c2970c4e

Check link from Plamen's answer.


PluralSight Learning Library

 

[gbaughma]

SQLBill:

It makes sense that if you concatenate together the SQL query then exec it that it would work.... that would have been my "fall back" had I not gotten it working. It did cross my mind, I just wasn't going to be beaten that easily.



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com