要实现的功能是将多行的数据记录转换成一行的记录,各数据用,分隔。由于行是不固定的,case when 或 decode就不行了在10i里面有内置函数wm_concat可以实现这个功能,但是9i不行。
请问在9i那如何实现?查看了10i函数,原型如下,估计加密了:create or replace function wmsys.wm_concat wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
8
58 96
+CuW1MAfZPVR6NOnXKVmXY2o9pswg8eZgcfLCNL+XhZy8K7/cgzcVrPnfMPnx3TAM7h0ZSXD
j57Asr2ym9ZtFldFmFu+RdpAU8VGORKSvtVG+DmAOR4C+NTa+Pit2kDbEpI5zhZUgqameSkE
DQ==
请问在9i那如何实现?查看了10i函数,原型如下,估计加密了:create or replace function wmsys.wm_concat wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
8
58 96
+CuW1MAfZPVR6NOnXKVmXY2o9pswg8eZgcfLCNL+XhZy8K7/cgzcVrPnfMPnx3TAM7h0ZSXD
j57Asr2ym9ZtFldFmFu+RdpAU8VGORKSvtVG+DmAOR4C+NTa+Pit2kDbEpI5zhZUgqameSkE
DQ==
select 区域 ,
substr(max(sys_connect_by_path(省份,',')),2) 省份
from ( select
区域,省份, row_number() over(order by 省份) rn
from re_room t1 )
)
start with rn=1
connect by rn-1=prior rn
group by 区域
这是我以前给人家写的,你看着修改就可以了
select job_id,
ltrim(max(sys_connect_by_path(employee_id,',')),',')
from (select
job_id,employee_id,
row_number() over (partition by job_id order by employee_id) rn
from employees)
group by job_id
start with rn=1
connect by prior rn=rn-1 and prior job_id=job_id