如何让T-SQL中的CASE函数返回多列？

T-SQL的CASE函数仅支持返回单个标量值，无法直接在THEN/ELSE后返回多列集合，你遇到的报错正是因为这个限制。下面提供两种可行的解决思路：

方法一：为每一列单独编写CASE逻辑 #

如果需要根据条件输出不同的列值组合，最直接的方式是给每个目标列分别设置CASE判断，确保每列都能根据条件返回对应的值：

SELECT
    CASE WHEN Street IS NOT NULL AND City IS NOT NULL THEN Name ELSE NULL END AS Name,
    CASE WHEN Street IS NOT NULL AND City IS NOT NULL THEN Surname ELSE NULL END AS Surname,
    CASE WHEN Street IS NOT NULL AND City IS NOT NULL THEN Street ELSE NULL END AS Street,
    CASE WHEN Street IS NOT NULL AND City IS NOT NULL THEN City ELSE NULL END AS City,
    CASE WHEN Street IS NOT NULL AND City IS NOT NULL THEN ZipCode ELSE NULL END AS ZipCode,
    CASE WHEN Street IS NULL OR City IS NULL THEN BackUpAddr ELSE NULL END AS BackUpAddr
FROM YourTable;

方法二：使用UNION ALL拆分条件查询 #

如果两种条件下的列结构差异较大，可以把两种场景拆分成两个独立查询，用UNION ALL合并结果（注意两个查询的列数、数据类型必须一致，差异列用NULL填充）：

-- 场景1：Street和City均不为空时返回指定列
SELECT
    Name,
    Surname,
    Street,
    City,
    ZipCode,
    NULL AS BackUpAddr
FROM YourTable
WHERE Street IS NOT NULL AND City IS NOT NULL

UNION ALL

-- 场景2：Street或City为空时返回备份地址
SELECT
    NULL AS Name,
    NULL AS Surname,
    NULL AS Street,
    NULL AS City,
    NULL AS ZipCode,
    BackUpAddr
FROM YourTable
WHERE Street IS NULL OR City IS NULL;

两种方法的适用场景：

• 方法一适合列结构一致，仅需根据条件切换值的场景，逻辑更紧凑；
• 方法二更适合两种条件下返回的列差异明显，或者需要分开处理复杂逻辑的场景。

内容的提问来源于stack exchange，提问作者neznajut