Posted by David Portas on 02/19/07 21:05

On 19 Feb, 20:46, bbl...@op.pl wrote:
> I'd like to change query:
>
> SELECT DM.*, 'condition1', NULL FROM DM
> WHERE (condition1)
> UNION
> SELECT DM.*, NULL, 'condition2' FROM DM
> WHERE (condition2)
>
> to one SELECT like this
>
> SELECT DM.*, WasCondition1, WasCondition2 FROM DM
> WHERE (condition1) or (condition2)
>
> but how to fill in the WasConditionX column?
> The UNION version was bad because in case a row fulfilled 2
> conditions, it was repeated instead of joining them, such like this:
>
> SELECT DM.*, 'condition1', 'condition2' FROM DM
> WHERE condition1 AND condition2
> UNION
> SELECT DM.*, 'condition1', NULL FROM DM
> WHERE condition1 AND NOT condition2
> UNION
> SELECT DM.*, NULL, 'condition2' FROM DM
> WHERE NOT condition1 AND condition2
>
> or (say DM has columns A, B, C):
>
> SELECT A, B, C, SUM(cond1), SUM(cond2) FROM
> (
> SELECT DM.*, 1 AS cond1, 0 AS cond2 FROM DM
> WHERE condition1
> UNION
> SELECT DM.*, 0 AS cond1, 1 AS cond2 FROM DM
> WHERE condition2
> ) AS DM
> GROUP BY A, B, C

Use a CASE expression. To adapt your second example:

SELECT col1, col2, col3,
CASE WHEN (condition1) THEN 'Y' ELSE 'N' END AS WasCondition1,
CASE WHEN (condition2) THEN 'Y' ELSE 'N' END AS WasCondition2
WHERE (condition1) OR (condition2) ;

Always avoid using SELECT *.


--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

[Back to original message]