求教oracle 行变列的函数

发布网友发布时间：2022-05-01 04:36

共3个回答

懂视网时间：2022-05-01 08:58

行转列自定义函数，只针对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自定义行转列函数

标签：

热心网友时间：2022-05-01 06:06

PS：以下直接复制，在Oracle中执行，我的只能作参考

--数据库的表
SELECT
LEVEL C1,
CASE
WHEN MOD(LEVEL, 2) = 1 THEN
'A'
ELSE
'B'
END AS C2,
CASE
WHEN MOD(LEVEL, 3) = 0 THEN
'A'
WHEN MOD(LEVEL, 3) = 1 THEN
'B'
ELSE
'C'
END AS C3
FROM DUAL
CONNECT BY LEVEL <= 10；

--参考实例如下：

select
A.C1,A.C3 A,B.C3 B
from
( SELECT
LEVEL C1,
CASE
WHEN MOD(LEVEL, 2) = 1 THEN
'A'
ELSE
'B'
END AS C2,
CASE
WHEN MOD(LEVEL, 3) = 0 THEN
'A'
WHEN MOD(LEVEL, 3) = 1 THEN
'B'
ELSE
'C'
END AS C3
FROM DUAL
CONNECT BY LEVEL <= 10
) A
LEFT JOIN
(SELECT
LEVEL C1,
CASE
WHEN MOD(LEVEL, 2) = 1 THEN
'A'
ELSE
'B'
END AS C2,
CASE
WHEN MOD(LEVEL, 3) = 0 THEN
'A'
WHEN MOD(LEVEL, 3) = 1 THEN
'B'
ELSE
'C'
END AS C3
FROM DUAL
CONNECT BY LEVEL <= 10
)B
ON A.C2 != B.C2
GROUP BY A.C1,A.C3,B.C3
ORDER BY C1追问C2列的值是不固定的，因为是程序里那列是手动输入的，不可能知道人家那列要写什么，所以sql中不可能存在‘A’,‘B’这种写死的东西，case when跟decode本身就差不多，这样是不可能的实现的

热心网友时间：2022-05-01 07:24

wm_concat().具体用法自己上网查追问看过，不知道怎么写，感觉没法实现