Oracle LISTAGG与Coalesce
Oracle LISTAGG与Coalesce
提问于 2020-09-13 00:22:59
我有下面的表EPS_PROPOSAL_EXT_T，我已经压缩了它：
+--------------------+------------+------------+
| PROPOSAL_NUMBER |ONR_OPTION_1|ONR_Option_2| ...
+--------------------+------------+------------+
| 1234 | N | N | ...
| 1235 | Y | Y | ...
| 1236 | N | Y | ...
+--------------------+------------+------------+这里的问题是，我需要在所有这些不同的LISTAGG上执行一个ONR_Options，但是我不能让它们以'Y'的形式出现。我需要用一些人类可以理解的东西来代替那些'Y'。请参见下面的SELECT语句，其中我尝试这样做：
SELECT eps.PROPOSAL_NUMBER, eps.TITLE, per.FULL_NAME, ext.NRP_IREF_CD, ext.RESEARCH_TYPE_CD,
nsf.NSF_CODE, ext.NPS_THRUST_DESCRIPTION,
CASE
WHEN ext.ONR_Option_1 = 'Y' THEN 'Option 1'
WHEN ext.ONR_Option_2 = 'Y' THEN 'Option 2'
WHEN ext.ONR_Option_3 = 'Y' THEN 'Option 3'
WHEN ext.ONR_Option_4 = 'Y' THEN 'Option 4'
WHEN ext.ONR_Option_5 = 'Y' THEN 'Option 5'
WHEN ext.ONR_Option_6 = 'Y' THEN 'Option 6'
WHEN ext.ONR_Option_7 = 'Y' THEN 'Option 7'
WHEN ext.ONR_Option_8 = 'Y' THEN 'Option 8'
WHEN ext.ONR_Option_9 = 'Y' THEN 'Option 9'
WHEN ext.ONR_NOT_APPLICABLE = 'Y' THEN 'Not Applicable'
ELSE ''
END ONR
FROM EPS_PROPOSAL eps
LEFT JOIN EPS_PROPOSAL_EXT_T ext
ON eps.PROPOSAL_NUMBER = ext.PROPOSAL_NUMBER
LEFT JOIN EPS_PROP_PERSON per
ON eps.PROPOSAL_NUMBER = per.PROPOSAL_NUMBER AND
(per.PROP_PERSON_ROLE_ID = 'PI' OR per.PROP_PERSON_ROLE_ID = 'PD')
LEFT JOIN EPS_PROP_ABSTRACT abs
ON eps.PROPOSAL_NUMBER = abs.PROPOSAL_NUMBER
LEFT JOIN NSF_CODES nsf
ON eps.NSF_CODE = nsf.NSF_SEQUENCE_NUMBER
WHERE eps.OWNED_BY_UNIT = '401' AND eps.requested_start_date_initial >= DATE '2019-10-01';这是可行的，但你可以看出问题所在，对吗？按照我设置CASE语句的方式，当一个特定的PROPOSAL_NUMBER有多个ONR_Option (即TRUE )时，它将不考虑。
在上面给出的示例中，如果我搜索一个编号=1235的提案；ONR结果应该是-‘选项1，选项2’。
如何用LISTAGG和COALESCE来解决这个难题？或者这就是解决问题的方法？
回答 1
Stack Overflow用户
回答已采纳
发布于 2020-09-13 17:48:15
LISTAGG在这里帮不上忙。为什么？因为您的数据模型是错误的。在我看来，应该是这样：
SQL> with test (prop, onr, status) as
2 (select 1234, '1', 'N' from dual union all
3 select 1234, '2', 'N' from dual union all
4 select 1234, '3', 'Y' from dual union all
5 select 1235, '1', 'Y' from dual union all
6 select 1235, '2', 'Y' from dual union all
7 select 1235, '3', 'N' from dual
8 )
9 select prop,
10 listagg(case when status = 'Y' then 'Option ' || onr end, ',')
11 within group (order by onr) as result
12 from test
13 group by prop;
PROP RESULT
---------- ------------------------------
1234 Option 3
1235 Option 1,Option 2
SQL>目前的情况是，看看这样做--将表中的CASE列和ONR_OPTION列连在一起，就像你想教它飞的山羊一样--再加上删除多个逗号的痛苦，是否有帮助：
SQL> with test (prop, onr_1, onr_2, onr_3) as
2 (select 1234, 'N', 'N', 'Y' from dual union all
3 select 1235, 'Y', 'Y', 'N' from dual
4 )
5 select prop,
6 trim(both ',' from
7 case when onr_1 = 'Y' then 'Option 1' else null end ||','||
8 case when onr_2 = 'Y' then 'Option 2' else null end ||','||
9 case when onr_3 = 'Y' then 'Option 3' else null end
10 ) as result
11 from test;
PROP RESULT
---------- ------------------------------
1234 Option 3
1235 Option 1,Option 2
SQL>页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/63866049
复制相关文章
相似问题
腾讯云开发者
