SQL Case Statement To Increment Value

Question

Trying to create a calculated column that would have a value based on the values in four other columns. Column 1, Coulmn 2, Column 3, Column 4 could be either Yes or No The end result in the calculated column, let's say called Progress, should be something along this line:

Progress = CASE
WHEN [Column1] = 'Yes' THEN Value+1
WHEN [Column2] = 'Yes' THEN Value+1
WHEN [Column3] = 'Yes' THEN Value+1
WHEN [Column4] = 'Yes' THEN Value+1
ELSE 0 END

Hope this makes sense as obviously the syntax for the above is not correct.

Answer 1

Sounds like this is what you're going for.

Progress = 
CASE WHEN [Column1] = 'Yes' THEN 1 ELSE 0 END +
CASE WHEN [Column2] = 'Yes' THEN 1 ELSE 0 END +
CASE WHEN [Column3] = 'Yes' THEN 1 ELSE 0 END + 
CASE WHEN [Column4] = 'Yes' THEN 1 ELSE 0 END 

This will return 0 + 1 for each "Yes".

Answer 2

In SQL Server:

SELECT  *
FROM    mytable
CROSS APPLY
        (
        SELECT  COALESCE(SUM(CASE val WHEN 'yes' THEN 1 END), 0)
        FROM    (VALUES (NULL)) q(v)
        UNPIVOT
                (
                val FOR col IN
                (column1, column2, column3, column4)
                ) c
        ) q (progress)

See SQLFiddle.