How to use the Union statement with a dynamic order by
I don’t use the Union statement to much in my SQL coding but today I needed to use it to combine results from two separate tables. But I ran into an error when I wanted to add in my usual code for doing a dynamic order by. SQL wouldn’t run my query and output this error message.
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.
To fix this problem I put my Union statement into a derived table by doing the following.
SELECT * FROM (
SELECT Column1, Column2 FROM Table_A
UNION ALL
SELECT Column1, Column2 FROM Table_B
) DerivedTableName (Column1, Column2)
Next I added in my dynamic Order By code as such.
SELECT * FROM (
SELECT Column1, Column2 FROM Table_A
UNION ALL
SELECT Column1, Column2 FROM Table_B
) DerivedTableName (Column1, Column2)
ORDER BY
CASE @OrderBy WHEN ‘Column1ASC’ THEN Column1 END ASC,
CASE @OrderBy WHEN ‘Column1DESC’ THEN Column1 END DESC