代码之家 › 专栏 › 技术社区 › Micho Rizo

带有名称/值列的oracle listagg

listagg pivot oracle

Micho Rizo · 技术社区 · 6 年前

我有如下表“foo”:

user_id attr_name    attr_value
-------------------------------
joe     user_id      abc123
joe     permission   A
joe     permission   B
joe     permission   C

我想要以下结果:

user_id       permission_List
-----------------------------
abc123        A, B, C

我可以通过以下fugly查询获得所需的结果:

select 
user_id,
LISTAGG(permissions,', ') within group(order by permissions) AS permission_list
from
(
    SELECT a1.attr_value AS user_id, 
    a2.attr_value AS permissions
    from foo a1 
    JOIN foo a2 on a1.user_id=a2.user_id
    WHERE a1.attr_name='user_id'
       and a2.attr_name='permission'
       and a1.user_id='joe'
)
group by user_id;

一定有更干净的方法!有什么建议吗?

1 回复 | 直到 6 年前

mathguy 6 年前

唉,PIVOT操作符不适用于listag,因此您仍然需要使用旧的数据透视方法(条件聚合)。但您可以简化查询,如下所示。

注意:在示例数据中,第一列名为USER\u ID,值为“john”,但随后有一个名为USER\u ID,值为“abc123”的属性。这毫无意义。我在输出中使用了NEW\u USER\u ID的列名,但我希望在现实生活中你不必忍受这种胡说八道。

您在下面看到的WITH子句不是解决方案的一部分;我把它包括进来只是为了测试。删除它,然后在其余的查询中,在所有地方使用实际的表名和列名。

with
  inputs ( user_id, attr_name, attr_value ) as (
    select 'joe', 'user_id'   , 'abc123' from dual union all
    select 'joe', 'permission', 'A'      from dual union all
    select 'joe', 'permission', 'B'      from dual union all
    select 'joe', 'permission', 'C'      from dual
  )
select   user_id,
         min(case attr_name when 'user_id' then attr_value end)      as new_user_id,
         listagg(case attr_name when 'permission' then attr_value end, ', ')
                                within group (order by attr_value)   as permission_list
from     inputs
where    attr_name in ('user_id', 'permission')
  and    user_id = 'joe'  --  if needed
group by user_id
;

USER_ID      NEW_USER_ID  PERMISSION_LIST   
------------ ------------ ------------------
joe          abc123       A, B, C

推荐文章

maddy · 如何根据oracle SQL中的某一列值进行排名

1 年前

kiric8494 · 显示以元音开头和结尾的城市名称

2 年前

Marko Masnikosa · 为什么SUBSTR不使用instr在结束索引处停止?

2 年前

Franz Biberkopf · Oracle:组合子查询和聚合函数

2 年前

BitLauncher · 甲骨文-如何模拟位列和布尔和/或?

2 年前

Arifullah · 如何从oracle中的列中删除特定的初始字符?

2 年前

Anar · Oracle SQL用户定义函数

2 年前

Ewerton Alves · 如何获取下一季度的数据-ORACLE SQL

2 年前

Pierre-olivier Gendraud · 如何在sql*plus中执行动态sql语句

2 年前

user1312312 · 如何为一组表编写通用触发器?

2 年前