I have a Query that's supposed to run like this -
If(var = xyz) SELECT col1, col2 ELSE IF(var = zyx) SELECT col2, col3 ELSE SELECT col7,col8 FROM . . .
How do I achieve this in T-SQL without writing separate queries for each clause? Currently I'm running it as
IF (var = xyz) { Query1 } ELSE IF (var = zyx) { Query2 } ELSE { Query3 }
That's just a lot of redundant code just to select different columns depending on a value. Any alternatives?
This question is related to
sql
sql-server
tsql
You are looking for the CASE statement
http://msdn.microsoft.com/en-us/library/ms181765.aspx
Example copied from MSDN:
USE AdventureWorks;
GO
SELECT ProductNumber, Category =
CASE ProductLine
WHEN 'R' THEN 'Road'
WHEN 'M' THEN 'Mountain'
WHEN 'T' THEN 'Touring'
WHEN 'S' THEN 'Other sale items'
ELSE 'Not for sale'
END,
Name
FROM Production.Product
ORDER BY ProductNumber;
GO
Try something like
SELECT
CASE var
WHEN xyz THEN col1
WHEN zyx THEN col2
ELSE col7
END AS col1,
...
In other words, use a conditional expression to select the value, then rename the column.
Alternately, you could build up some sort of dynamic SQL hack to share the query tail; I've done this with iBatis before.
CASE is the answer, but you will need to have a separate case statement for each column you want returned. As long as the WHERE clause is the same, there won't be much benefit separating it out into multiple queries.
Example:
SELECT
CASE @var
WHEN 'xyz' THEN col1
WHEN 'zyx' THEN col2
ELSE col7
END,
CASE @var
WHEN 'xyz' THEN col2
WHEN 'zyx' THEN col3
ELSE col8
END
FROM Table
...
Simple CASE expression:
CASE input_expression
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END
Searched CASE expression:
CASE
WHEN Boolean_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END
Reference: http://msdn.microsoft.com/en-us/library/ms181765.aspx
The most obvious solutions are already listed. Depending on where the query is sat (i.e. in application code) you can't always use IF statements and the inline CASE statements can get painful where lots of columns become conditional. Assuming Col1 + Col3 + Col7 are the same type, and likewise Col2, Col4 + Col8 you can do this:
SELECT Col1, Col2 FROM tbl WHERE @Var LIKE 'xyz'
UNION ALL
SELECT Col3, Col4 FROM tbl WHERE @Var LIKE 'zyx'
UNION ALL
SELECT Col7, Col8 FROM tbl WHERE @Var NOT LIKE 'xyz' AND @Var NOT LIKE 'zyx'
As this is a single command there are several performance benefits with regard to plan caching. Also the Query Optimiser will quickly eliminate those statements where @Var doesn't match the appropriate value without touching the storage engine.
Source: Stackoverflow.com