Oracle自定义行转列函数
时间：2021-07-01 10:21:17
帮助过：2人阅读
行转列自定义函数，只针对TABLE1表
--paramType是参数类型，用于判断，param1和param2是条件参数
create or replace function My_concat(paramType
in integer,param1
in varchar2,param2
in varchar2)
return varchar2
is
resultStr varchar2(
2000);
begin
if paramType
= 1 then
--定义游标
for temp_cursor1
in (
select CONTA_ID
FROM TABLE1
WHERE FORM_ID
=param1
and orderType
=param2)
loop
resultStr:=resultStr
||temp_cursor1.CONTA_ID
||‘,‘;
end loop;
elsif paramType = 2 then
for temp_cursor2
in (
select CONTA_TYPE
FROM TABLE1
WHERE FORM_ID
=param1
and orderType
=param2)
loop
resultStr:=resultStr
||temp_cursor2.CONTA_TYPE
||‘,‘;
end loop;
elsif paramType = 3 then
for temp_cursor3
in (
select CONTA_WEIGHT
FROM TABLE1
WHERE FORM_ID
=param1
and orderType
=param2)
loop
resultStr:=resultStr
||temp_cursor3.CONTA_WEIGHT
||‘,‘;
end loop;
end if;
resultStr:=rtrim(resultStr,
‘,‘);
return resultStr;
end;
--执行例子：
select A.COLUMN1,A.COLUMN2,A.COLUMN3
My_concat(1,A.COOLUMN1,A.COLUMN2)
AS CONTA_IDS,
My_concat(2,A.COOLUMN1,A.COLUMN2)
AS CONTA_TYPES,
My_concat(3,A.COOLUMN1,A.COLUMN2)
AS CONTA_WEIGHTS
from TABLE1
AS B
LEFT JOIN TABLE2 A
ON A.COOLUMN1
=B.FORM_ID
AND A.COLUMN2
=B.orderType
结果如下图：
Oracle自定义行转列函数
标签：
