A personal repository of technical notes. - CSC

SQL Conditional Sorting with Dynamic ORDER BY

Dynamic SQL Sorting with ORDER BY
  

DECLARE @sortByColumn varchar(100)

SELECT @sortByColumn = 'MyColumn05'-- 'MyColumn01'-- 'X'--

-- Dynamic Sorting Column in SELECT

SELECT

MyColumn01

,MyColumn02

,(MyColumn03 * MyColumn04)

,MyColumn05

,CASE @sortByColumn

WHEN 'MyColumn01' THEN MyColumn01

WHEN 'MyColumn02' THEN MyColumn02

WHEN 'MyColumn05' THEN STR(MyColumn05,19,16)

ELSE MyColumn01

END AS SorterColumn

FROM MyTableA

ORDER BY SorterColumn

-- Dynamic Sorting Column in ORDER BY

SELECT

MyColumn01

,MyColumn02

,(MyColumn03 * MyColumn04) AS NewColumn -- Calculation duplicated below in ORDER BY

,MyColumn05

FROM MyTableA

ORDER BY

CASE @sortByColumn

WHEN 'MyColumn01' THEN MyColumn01

WHEN 'MyColumn02' THEN MyColumn02

WHEN 'NewColumn' THEN STR(MyColumn03 * MyColumn04) -- Calculation duplicated above in SELECT

WHEN 'MyColumn05' THEN STR(MyColumn05,19,16)

ELSE MyColumn01

END

SELECT

MyColumn01

,MyColumn02

FROM MyTableA

ORDER BY

CASE

WHEN MyColumn01 > 0 THEN 0

ELSE 1

END

,MyColumn02

--------------------------------------------------

DECLARE @sortDesc bit

SELECT @sortDesc = 1-- 0--

-- Dynamic DESCENDING / ASCENDING

SELECT AsOfDate,MyColumn01,MyColumn05

FROM MyTableA

WHERE AsOfDate = '7-31-2007'

ORDER BY

CASE WHEN @sortDesc = 0 THEN MyColumn01 END ASC,

CASE WHEN @sortDesc = 1 THEN MyColumn01 END DESC

-- Dynamic DESCENDING / ASCENDING with Dynamic Sorting Column in ORDER BY

SELECT

MyColumn01

,MyColumn02

,(MyColumn03 * MyColumn04) AS NewColumn -- Calculation duplicated below in ORDER BY

,MyColumn05

FROM MyTableA

LEFT OUTER JOIN MyTableB

ON FM_TICKER = MyColumn01

WHERE AsOfDate = '7-31-2007'

ORDER BY

-- Ascending Sorts

CASE WHEN @sortDesc = 0 AND @sortByColumn = 'MyColumn01' THEN MyColumn01 END ASC,

CASE WHEN @sortDesc = 0 AND @sortByColumn = 'MyColumn02' THEN MyColumn02 END ASC,

CASE WHEN @sortDesc = 0 AND @sortByColumn = 'NewColumn' THEN STR(MyColumn03 * MyColumn04) END ASC, -- Calculation duplicated above in SELECT

CASE WHEN @sortDesc = 0 AND @sortByColumn = 'MyColumn05' THEN STR(MyColumn05,19,16) END ASC,

-- Descending Sorts

CASE WHEN @sortDesc = 1 AND @sortByColumn = 'MyColumn01' THEN MyColumn01 END DESC,

CASE WHEN @sortDesc = 1 AND @sortByColumn = 'MyColumn02' THEN MyColumn02 END DESC,

CASE WHEN @sortDesc = 1 AND @sortByColumn = 'NewColumn' THEN STR(MyColumn03 * MyColumn04) END DESC, -- Calculation duplicated above in SELECT

CASE WHEN @sortDesc = 1 AND @sortByColumn = 'MyColumn05' THEN STR(MyColumn05,19,16) END DESC

-- WARNING: Dynamic DESCENDING / ASCENDING **DOES NOT WORK** with Dynamic Sorting Column in SELECT

SELECT AsOfDate,MyColumn01,MyColumn05

,CASE @sortByColumn

WHEN 'MyColumn01' THEN MyColumn01

WHEN 'MyColumn05' THEN STR(MyColumn05,19,16)

END AS SorterColumn

FROM MyTableA

WHERE AsOfDate = '7-31-2007'

ORDER BY

CASE WHEN @sortDesc = 0 THEN SorterColumn END ASC,

CASE WHEN @sortDesc = 1 THEN SorterColumn END DESC

No comments:

Post a Comment