当前返回的查询数据如下:
UserName Op1 Op2 Op3
-------- --- --- ---
User1 1 0 0
User2 1 1 1
User3 1 1 0
结果应该只是一行:
Ops1 Ops2 Ops3
---- ---- ----
User1, User2, User3 User2, User3 User2
如何在Oracle中做到这一点?
这是一种使用方法LISTAGG()
(我认为它需要Oracle 11.2或更高版本):
with
test_data ( username, op1, op2, op3 ) as (
select 'User1', 1, 0, 0 from dual union all
select 'User2', 1, 1, 1 from dual union all
select 'User3', 1, 1, 0 from dual
)
select listagg(case op1 when 1 then username end, ',')
within group (order by username) as ops1,
listagg(case op2 when 1 then username end, ',')
within group (order by username) as ops2,
listagg(case op3 when 1 then username end, ',')
within group (order by username) as ops3
from test_data
;
OPS1 OPS2 OPS3
------------------ ------------------ ------------------
User1,User2,User3 User2,User3 User2
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句