对单个CASE语句使用多个THEN? [英] Multiple THEN to a single CASE statement?

问题描述

我想添加一个额外的列.该额外的列将基于案例陈述获得一个值.我的目标是，例如，尽管语法非常错误，但是可以实现此目的:当"A"时，"Apple"和"ExtraColumn" = 1.我尝试使用下面的代码为此创建一个等价物，但是我收到一个错误，提示我的SQL语法不正确.

I want to add an extra column. This extra column will get a value based on a case statment. My objective is for instance, although the syntax is very wrong, something to accomplish this: When 'A' then 'Apple' AND 'ExtraColumn'=1. I have tried to create an equvialnt to this using the code below, but I get an error that my SQL syntax is incorrect.

select Patient,
case ColumnName 
when 'A' then 'Apple'  
when 'B' then 'Banana'
end ColumnName, 
case ExtraColumn 
when 'A' then '1'  
when 'B' then '2'
end ExtraColumn, 

ColumnResult
from TABLE

unpivot
(
ColumnResult

for ColumnName in (COL1,COL2,COL3)

for ExtraColumn in (COL1,COL2,COL3)

)u

推荐答案

您必须为每个列名称重复大小写构造.像这样:

You have to repeat your case construct for each column name. Something like this:

case ColumnName 
when 'A' then 'Apple'  
when 'B' then 'Banana'
end ColumnName, 

case ColumnName
when 'A' then '1'  
when 'B' then '2'
end ExtraColumn,

这里有一个陷阱.如果在where子句中使用ColumnName，则可能不喜欢结果，因为您将其用作别名.

There is a gotcha here. If you use ColumnName in your where clause, you might not like the results because you used it as an alias.

编辑从此处开始

您可以根据需要设置别名.如果它们很简单，只需键入它们即可.

You can make your aliases whatever you want. If they are simple, just type them.

 select column1 fred, column2 barney

如果您想要多个单词或一个sql关键字，请使用双引号

If you want more than one word, or an sql keyword, use double quotes

select column1 "fred flinstone", column2 "select"

这篇关于对单个CASE语句使用多个THEN?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！