英文:
How to make a CASE function return multiple columns?
问题
在T-SQL中，CASE函数无法直接返回多列。您可以使用CASE函数来确定要返回的单个列，然后在查询中选择多个列。以下是一种可能的解决方法：
SELECT
CASE
WHEN Street IS NOT NULL AND City IS NOT NULL
THEN Name
ELSE BackUpAddr
END AS Name,
CASE
WHEN Street IS NOT NULL AND City IS NOT NULL
THEN Surname
ELSE BackUpAddr
END AS Surname,
CASE
WHEN Street IS NOT NULL AND City IS NOT NULL
THEN Street
ELSE BackUpAddr
END AS Street,
CASE
WHEN Street IS NOT NULL AND City IS NOT NULL
THEN City
ELSE BackUpAddr
END AS City,
CASE
WHEN Street IS NOT NULL AND City IS NOT NULL
THEN ZipCode
ELSE BackUpAddr
END AS ZipCode
FROM Table
这样，您可以根据条件在每个CASE语句中选择不同的列，并将它们作为单独的列返回。
英文:
Us there a way to return multiple columns from the CASE function in T-SQL? The query I tried:
SELECT
CASE
WHEN Street IS NOT NULL AND City IS NOT NULL
THEN Name, Surname, Street, City, ZipCode
ELSE BackUpAddr
END
FROM Table
But as expected after "THEN" I am allowed to enter only one column. Any ideas on how to solve the problem?
答案1
得分: 1
以下是已翻译的内容：
你可以为要返回的每一列使用多个CASE语句：
SELECT
Name,
Surname,
CASE WHEN Street IS NOT NULL THEN Street ELSE BackUpAddr END AS
Street,
CASE WHEN City IS NOT NULL THEN City ELSE BackUpAddr END AS City,
ZipCode
FROM Table
英文:
You can use multiple CASE statements for each column that you want to return:
SELECT
Name,
Surname,
CASE WHEN Street IS NOT NULL THEN Street ELSE BackUpAddr END AS
Street,
CASE WHEN City IS NOT NULL THEN City ELSE BackUpAddr END AS City,
ZipCode
FROM Table
答案2
得分: 1
第一个问题在于，每个case表达式的选项都会产生不同数量的结果。由于这是针对每一行的，您所要求的是可能具有不同列数的结果。这是不被允许的。结果集中的每一行必须具有相同数量（和类型）的列。
您无法绕过这个问题。无论您使用CASE还是其他方法，结果中的列必须保持一致。没有例外。要么您需要为选择BackUpAddr列的情况添加大量的额外的null值，要么您需要将所有内容连接成一个单独的长字符串，以应对选择其他列的情况。
第二个问题是误解case表达式实际上是表达式。这是一个常见的错误。这意味着case表达式不像过程性代码中的if块。相反，case表达式的结果必须始终是单个（标量）值。
英文:
The first issue here is each option from the case expression produces a different number of results. Since this is per row, what you're asking for is results that might have varying numbers of columns. That's not allowed. Every row in a result set MUST have the same number (and type) of columns.
You won't be able to find a way around this. Whether you use CASE or something else, the columns in a result have to be consistent. No exceptions. Either you'll need to a bunch of extra null values for the columns in the case where you choose the BackUpAddr column, or you'll need to concatenate everything into a single long string for the case where you choose the other columns.
The second issue is misunderstanding that case expressions really are expressions. This is a common mistake. What this means is a case expression is not like an if block in procedural code. Instead, the result of a case expression must always be a single (scalar) value.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论