CODEWITHSUNDEEP
sqlsql-serversorting
markpsmith
markpsmith

Reputation: 4918

SQL Server dynamic sorting on multiple columns

I have a stored procedure which uses dynamic sorting, 2 parameters determine the sorting - column: @SortIndex and sort direction: @SortDirection

relevant code:

...
ROW_NUMBER() OVER
            (
            ORDER BY
            --  string order by
            CASE @SortDirection
                  WHEN 'ASC'  THEN
                    CASE @SortIndex
                      WHEN 1 THEN SKU
                      WHEN 2 THEN BrandName
                      WHEN 3 THEN ItemName
                    END            
                END ASC,
                CASE @SortDirection
                  WHEN 'DESC' THEN
                    CASE @SortIndex
                      WHEN 1 THEN SKU
                      WHEN 2 THEN BrandName
                      WHEN 3 THEN ItemName
                    END
                END DESC,

This sorts on single columns, but I want to sort on BrandName ASC, ItemName ASC when @SortIndex is 2.

Upvotes: 0

Views: 4852

Answers (3)

Keyvan AryaeeMoeen
Keyvan AryaeeMoeen

Reputation: 11

A sample for generality....By K.AryaeeMoeen

---------------------------------------------------
SELECT 1 AS Num, '2015-06-22' AS Datex INTO Arya
INSERT INTO Arya 
SELECT 2, '2015-08-17' UNION SELECT 3, '2015-07-14'
---------------------------------------------------
Now, Dynamic sorting(Base on Datex Field) in a SELECTION ....
---------------------------------------------------
SELECT Num, Date1 FROM ARYA, (SELECT -1 as e union Select 1 as e) a
WHERE a.e=-1  --(OR a.e=1) For Dynamic Sort
ORDER BY DATEDIFF(DAY, '2000-01-01', Arya.Datex)*sign(a.e)
---------------------------------------------------

Upvotes: -2

Kevin Cook
Kevin Cook

Reputation: 1932

ROW_NUMBER() OVER
            (
            ORDER BY
            --  string order by
            CASE @SortDirection
                  WHEN 'ASC'  THEN
                    CASE @SortIndex
                      WHEN 1 THEN SKU
                      WHEN 2 THEN BrandName + ',' + ItemName 
                      WHEN 3 THEN ItemName
                    END            
                END ASC,
                CASE @SortDirection
                  WHEN 'DESC' THEN
                    CASE @SortIndex
                      WHEN 1 THEN SKU
                      WHEN 2 THEN BrandName + ',' + ItemName
                      WHEN 3 THEN ItemName
                    END
                END DESC,

Use Brandname + ItemName in the When 2 Clause and to have both fields be used in the sort.

Upvotes: 0

C.J.
C.J.

Reputation: 3527

If you cannot use Dynamic SQL, the only way is to list all the possible combination for ASC and DESC

For example:

ORDER By
CASE WHEN @SortIndex = '1' AND @SortDirection = 'ASC' THEN SKU END,
CASE WHEN @SortIndex = '1' AND @SortDirection = 'DESC' THEN SKU END DESC,
CASE WHEN @SortIndex = '2' AND @SortDirection = 'ASC' THEN BrandName END,
CASE WHEN @SortIndex = '2' AND @SortDirection = 'DESC' THEN BrandName END DESC,
--and so on...

Upvotes: 3

Related Questions