Geek asks and answers

SQL state statement for added value

Trying to create a calculated column that will have a value based on the values ​​in four other columns. Column 1, Coulmn 2, column 3, column 4 may be yes or no The final result in the calculated column, for example, called Progress, should be something like 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

I hope this makes sense, because obviously the syntax of the above is incorrect.

+5
source share
2 answers

Looks like this is what you are going to do.

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".

+5
source

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 .

+1
source

More articles:


All Articles