SQL案例声明增加值

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:

尝试创建一个计算列,该列的值基于其他四列中的值。第1列,第2列,第3列,第4列可以是“是”或“否”计算列中的最终结果,比如称为“进度”,应该是这一行:

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.

希望这很有意义,因为上面的语法显然不正确。

2 个解决方案

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

这将为每个“是”返回0 + 1。

#2

In SQL Server:

在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