第1个回答 2012-07-11
参考 oracle11g行变列两种办法的比较:
方法1：优点是分析函数，缺点：返回log，低效，
select link_pid, replace(strs,',','|') all_forms
from
(select t.link_pid,
wmsys.wm_concat(t.form_of_way)
over(partition by t.link_pid order by t.form_of_way ) strs,
row_number() over(partition by t.link_pid order by t.form_of_way) RN,
count(1) over(partition by t.link_pid ) CNT
from rd_link_form t
) where RN=CNT
方法2：优点是高效，返回char，缺点：不是分析函数
select t.link_pid,
listagg(t.form_of_way, '|')
within group (order by t.form_of_way ) all_forms
from rd_link_form t
group by t.link_pid
listagg()需在11.2版本下才行
两种方法的比较：依link_pid去重后有23万的数据量，方法1的wmsys.wm_concat用3小时看不到结果，方法2的listagg用80秒看到结果
【我以字典表user_tab_columns改写了上述SQL，如下，大家直接执行就行：】
方法1：
select table_name, replace(strs, ',', '|') all_forms
from (select t.table_name,
wmsys.wm_concat(t.COLUMN_NAME) over(partition by t.table_name order by t.COLUMN_NAME) strs,
row_number() over(partition by t.table_name order by t.COLUMN_NAME) RN,
count(1) over(partition by t.table_name) CNT
from user_tab_columns t)
where RN = CNT
方法2：
select t.table_name,
listagg(t.COLUMN_NAME, '|')
within group (order by t.COLUMN_NAME ) all_forms
from user_tab_columns t
group by t.table_name
大家可以直接在自己的pl/sql中运行上述SQL