3,491
社区成员
发帖
与我相关
我的任务
分享
CREATE OR REPLACE FUNCTION SPLIT(SRC_STR IN VARCHAR2,
SPLIT_STR VARCHAR2) RETURN MY_TABLE_TYPE IS
V_TABLE_STR MY_TABLE_TYPE := MY_TABLE_TYPE();
V_TEMP_STR VARCHAR2(8000) := SRC_STR;
V_SPLIT_STR VARCHAR2(20) := SPLIT_STR;
I NUMBER := 1;
J NUMBER := 1;
BEGIN
IF V_SPLIT_STR IS NULL THEN
V_SPLIT_STR := ',';--我们默认用逗号分隔
END IF;
IF SRC_STR IS NULL OR SRC_STR = V_SPLIT_STR THEN
RETURN V_TABLE_STR;
END IF;
V_TEMP_STR := LTRIM(V_TEMP_STR, V_SPLIT_STR);
LOOP
I := INSTR(V_TEMP_STR, V_SPLIT_STR, J);
EXIT WHEN I = 0 OR J > LENGTH(V_TEMP_STR);
V_TABLE_STR.EXTEND;
V_TABLE_STR(V_TABLE_STR.COUNT) := SUBSTR(V_TEMP_STR, J, I - J);
J := I + LENGTH(V_SPLIT_STR);
END LOOP;
IF J < LENGTH(V_TEMP_STR) THEN
V_TABLE_STR.EXTEND;
V_TABLE_STR(V_TABLE_STR.COUNT) := SUBSTR(V_TEMP_STR, J, LENGTH(V_TEMP_STR) - J + 1);
END IF;
RETURN V_TABLE_STR;
END SPLIT;
select decode(c.rn,1,t1,2,t2,3,t3,4,t4)
from (select a.*,b.rn from lhx_tmp a,
(select rownum rn from dual connect by rownum < 5) b) c
order by 1
WITH TEMP AS
(SELECT '1' T1,
'2' T2,
'3' T3,
'4' T4
FROM DUAL)
SELECT *
FROM TEMP UNPIVOT(T_VALUE FOR T_TYPE IN(T1 AS 'T1', T2 AS 'T2',
T3 AS 'T3', T4 AS 'T4'))
WITH TEMP AS
(SELECT 'T1' T_TYPE,'1' T_VALUE FROM DUAL UNION
SELECT 'T2' T_TYPE, '2' T_VALUE FROM DUAL UNION
SELECT 'T3' T_TYPE, '3' T_VALUE FROM DUAL UNION
SELECT 'T4' T_TYPE, '4' T_VALUE FROM DUAL )
SELECT *
FROM TEMP PIVOT(MAX(T_VALUE) FOR T_TYPE IN('T1' AS T1, 'T2' AS T2,
'T3' AS T3, 'T4' AS T4))
union all --吧
with temp as(
select '1' t1,'2' t2,'3' t3,'4' t4 from dual
)
select t1 from temp
union all
select t2 from temp
union all
select t3 from temp
union all
select t4 from temp