sql - 将多个列值合并为一个并在多行中打印一个字符串 - oracle SQL
问题描述
我不知道从哪里开始这个查询。Stuff() 函数无助于获得我想要的结果。任何帮助表示赞赏。
我的桌子:
+-----+-----------+--------+
| uid | uname | host |
+-----+-----------+--------+
| 1 | testuser | host 1 |
| 2 | testuser | host 2 |
| 3 | testuser2 | host 3 |
| 4 | testuser2 | host 4 |
+-----+-----------+--------+
预期输出:
+-----+-----------+--------+---------------+
| uid | uname | host | combined host |
+-----+-----------+--------+---------------+
| 1 | testuser | host 1 | host1,host2 |
| 2 | testuser | host 2 | host1,host2 |
| 3 | testuser2 | host 3 | host3,host4 |
| 4 | testuser2 | host 4 | host3,host4 |
+-----+-----------+--------+---------------+
解决方案
使用LISTAGG
和子查询连接
with cte as
(
select 1 uid1 ,'testuser' as uname,'host 1' as host from DUAL union all
select 2 uid1 ,'testuser' as uname,'host 2' as host from DUAL union all
select 3 uid1 ,'testuser2' as uname,'host 3' as host from DUAL union all
select 4 uid1 ,'testuser2' as uname,'host 4' as host from DUAL
)
select cte.uname,cte.host,val from cte join (
select uname,LISTAGG(host,',') within group (order by host) as val
from cte group by uname) t on cte.uname=t.uname
UNAME HOST VAL
testuser host 1 host 1,host 2
testuser host 2 host 1,host 2
testuser2 host 3 host 3,host 4
testuser2 host 4 host 3,host 4