行列转换：使用聚合函数、sys_connect_by_path、wm

本文探讨了SQL中聚合函数的应用，包括如何使用GROUP BY和聚合函数进行数据分组，利用wm_sys.wm_concat和sys_connect_by_path进行字符串聚合，以及通过修改SQL语句提高查询效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

①聚合函数，当然也可以用自定义函数实现，主要是利用了GROUP BY和聚合函数分组。

修改前：

select distinct sn.s_num, con1.flag con_flag, con1.id con_id, con1.name con_name, con1.mobile con_mobile, con2.flag con2_flag, con2.id con2_id, con2.name con2_name, con2.mobile con2_mobile, s.id s_id, s.name stu_name

from table_snumber sn, table_c con1, table_c con2, table_s s

where sn.stu_id(+)=s.id

and con2.r_no(+)=2

and con2.s_id(+)=s.id

and con1.r_no(+)=1

and con1.s_id(+)=s.id

and s.u_id=:1
order by sn.s_num, s.name

修改后：

SELECT SN.S_NUM,
MAX(DECODE(C.R_NO,1,C.FLAG,NULL)),
MAX(DECODE(C.R_NO,1,C.ID,NULL)),
MAX(DECODE(C.R_NO,1,C.NAME,NULL)),
MAX(DECODE(C.R_NO,1,C.MOBILE,NULL)),
MAX(DECODE(C.R_NO,2,C.FLAG,NULL)),
MAX(DECODE(C.R_NO,2,C.ID,NULL)),
MAX(DECODE(C.R_NO,2,C.NAME,NULL)),
MAX(DECODE(C.R_NO,2,C.MOBILE,NULL)),S.ID S_ID,S.NAME STU_NAME
FROM STU_NUMBER SN, CONNECTOR C, STUDENT S
WHERE SN.S_ID(+)=S.ID
AND C.S_ID(+)=S.ID
AND S.UNIT_ID=665543--:1
GROUP BY SN.S_NUM,S.ID,S.NAME
ORDER BY SN.S_NUM, S.NAME

-----少关联了TABLE_C和少了一个distinct。

②使用wm_sys.wm_concat

select *
from dba_source d
where d.type='FUNCTION'
and d.name='WM_CONCAT'

-----已经加密过那么怎么用？咕狗或者用PL/SQL看一下。

select wmsys.wm_concat(c.MOBILE),wmsys.wm_concat(c.NAME),wmsys.wm_concat(c.id)
from TABLE_C C
where c.U_ID=665543
group by c.S_ID（还有个order by选项）

-------输出格式为

13500000000,1590000000 张之夏,庄之俊 5941820,5941821

与要求格式不太相符也是可以处理的还是选择聚合函数

③使用sys_connect_by_path

不再测试。

详见：

http://www.ningoo.net/html/2008/how_to_do_string_aggregate_on_oracle.html