Oracle数据库中如何将CASE语句多值拼接至单列

原查询通过CASE语句只能返回单个匹配的市政服务名称，现需求将所有符合条件的服务名称拼接成('值1','值2','值3')的格式，同时保留用户ID和地区信息，输出示例如下：

User_id  Municipal_Services_Do_you_use                                  State
142351   ('房产税评估', '房产税缴纳','出生与死亡证明')                  德里

原查询代码：

select
 case
   when s.X344731='Y' then 'Property Tax Assessment'
   when s.X344732='Y' then 'Property Tax Payment'
   when s.X344733='Y' then 'Birth and Death Certificate'
   when s.X344734='Y' then ' Trade License'
   when s.X344735='Y' then ' Complaints/Grievance Redressal'
   when s.X344736='Y' then 'Water Connection'
   when s.X344737='Y' then 'Sewerage Connection'
   when s.X344738='Y' then 'Online Building Plan Approval System'    
   when s.X344739='Y' then ' Fire NOC'    
   when s.X3447310='Y' then ' NOC (Others)'    
   when s.X3447311='Y' then 'Certificates (Others)'    
   when s.X3447312='Y' then 'User Charges (water charges, parking fee, advertisement charges, electricity charges etc. )'    
   when s.X3447313='Y' then 'All of the above'    
   when s.X3447314='Y' then 'None of the above'    
   else 'Not Answered' end as municipal_services_do_you_use    
from lime_survey_988773 s 
left join lime_tokens_988773 t 
on (s.token=t.token)     
where s.submitdate is not null 
  and user_id=143561; 

解决方案（中文服务名称） #

下面提供两种实现方式，均已将服务名称翻译为中文，并处理了特殊选项的优先级：

方式一：简洁字符串拼接法 #

直接通过多个CASE语句拼接符合条件的服务名称，最后整理成目标格式：

select
    t.user_id,
    case
        -- 优先处理全选/全不选
        when s.X3447313 = 'Y' then '(''以上全部'')'
        when s.X3447314 = 'Y' then '(''以上都不'')'
        else
            -- 拼接所有符合条件的服务名称，再处理格式
            case 
                when concat_str is null then '(''未作答'')'
                else '(' || concat_str || ')'
            end
    end as municipal_services_do_you_use,
    t.state
from (
    select
        t.user_id,
        t.state,
        s.X3447313,
        s.X3447314,
        -- 逐个判断字段，拼接符合条件的服务名称
        trim(trailing ', ' from 
            case when s.X344731='Y' then '''房产税评估'', ' else '' end ||
            case when s.X344732='Y' then '''房产税缴纳'', ' else '' end ||
            case when s.X344733='Y' then '''出生与死亡证明'', ' else '' end ||
            case when s.X344734='Y' then '''营业执照'', ' else '' end ||
            case when s.X344735='Y' then '''投诉/申诉处理'', ' else '' end ||
            case when s.X344736='Y' then '''供水接入'', ' else '' end ||
            case when s.X344737='Y' then '''排污接入'', ' else '' end ||
            case when s.X344738='Y' then '''在线建筑规划审批系统'', ' else '' end ||
            case when s.X344739='Y' then '''消防无异议证明（NOC）'', ' else '' end ||
            case when s.X3447310='Y' then '''其他无异议证明（NOC）'', ' else '' end ||
            case when s.X3447311='Y' then '''其他证明'', ' else '' end ||
            case when s.X3447312='Y' then '''用户收费（水费、停车费、广告费、电费等）'', ' else '' end
        ) as concat_str
    from lime_survey_988773 s
    left join lime_tokens_988773 t on s.token = t.token
    where s.submitdate is not null
      and t.user_id = 143561
) temp;

方式二：子查询关联+LISTAGG聚合 #

通过子查询定义所有服务选项，关联主表后用聚合函数拼接结果，适合后续需要扩展服务选项的场景：

select
    t.user_id,
    case
        when s.X3447313 = 'Y' then '(''以上全部'')'
        when s.X3447314 = 'Y' then '(''以上都不'')'
        else
            '(' || LISTAGG('''' || service_name || '''', ', ') WITHIN GROUP (ORDER BY service_order) || ')'
    end as municipal_services_do_you_use,
    t.state
from lime_survey_988773 s
left join lime_tokens_988773 t on s.token = t.token
-- 子查询定义所有服务选项及对应字段
left join (
    select 1 as service_order, 'X344731' as col_name, '房产税评估' as service_name from dual
    union all select 2, 'X344732', '房产税缴纳' from dual
    union all select 3, 'X344733', '出生与死亡证明' from dual
    union all select 4, 'X344734', '营业执照' from dual
    union all select 5, 'X344735', '投诉/申诉处理' from dual
    union all select 6, 'X344736', '供水接入' from dual
    union all select 7, 'X344737', '排污接入' from dual
    union all select 8, 'X344738', '在线建筑规划审批系统' from dual
    union all select 9, 'X344739', '消防无异议证明（NOC）' from dual
    union all select 10, 'X3447310', '其他无异议证明（NOC）' from dual
    union all select 11, 'X3447311', '其他证明' from dual
    union all select 12, 'X3447312', '用户收费（水费、停车费、广告费、电费等）' from dual
) services on 
    (services.col_name = 'X344731' and s.X344731 = 'Y')
    or (services.col_name = 'X344732' and s.X344732 = 'Y')
    or (services.col_name = 'X344733' and s.X344733 = 'Y')
    or (services.col_name = 'X344734' and s.X344734 = 'Y')
    or (services.col_name = 'X344735' and s.X344735 = 'Y')
    or (services.col_name = 'X344736' and s.X344736 = 'Y')
    or (services.col_name = 'X344737' and s.X344737 = 'Y')
    or (services.col_name = 'X344738' and s.X344738 = 'Y')
    or (services.col_name = 'X344739' and s.X344739 = 'Y')
    or (services.col_name = 'X3447310' and s.X3447310 = 'Y')
    or (services.col_name = 'X3447311' and s.X3447311 = 'Y')
    or (services.col_name = 'X3447312' and s.X3447312 = 'Y')
where s.submitdate is not null
  and t.user_id = 143561
group by t.user_id, t.state, s.X3447313, s.X3447314
-- 补充未作答的情况
union all
select
    t.user_id,
    '(''未作答'')' as municipal_services_do_you_use,
    t.state
from lime_survey_988773 s
left join lime_tokens_988773 t on s.token = t.token
where s.submitdate is not null
  and t.user_id = 143561
  and s.X344731 != 'Y' and s.X344732 != 'Y' and s.X344733 != 'Y' and s.X344734 != 'Y'
  and s.X344735 != 'Y' and s.X344736 != 'Y' and s.X344737 != 'Y' and s.X344738 != 'Y'
  and s.X344739 != 'Y' and s.X3447310 != 'Y' and s.X3447311 != 'Y' and s.X3447312 != 'Y'
  and s.X3447313 != 'Y' and s.X3447314 != 'Y';

核心逻辑说明 #

1. 特殊选项优先：先判断“以上全部”或“以上都不”，直接返回对应格式的结果；
2. 格式处理：Oracle中用两个单引号''表示转义的单引号，最终拼接成('值1','值2')的格式；
3. 未作答场景：当所有服务字段都不为'Y'时，返回“未作答”。

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