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
ORACLE 11g 新函数: 行转列: 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))
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
SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ';')), 2) q FROM (SELECT a.card_code, a.q, row_number() over(PARTITION BY a.card_code ORDER BY a.q) rn FROM t_change_lc_comma a) t1 START WITH t1.rn = 1 CONNECT BY t1.card_code = PRIOR t1.card_code AND t1.rn - 1 = PRIOR t1.rn GROUP BY t1.card_code;
没必要搞这么复杂,直接union all就行了
LZ是要将行转成列是吧,如果你要做一个较为通用的,我也有办法,因为曾经研究对于动态绑定InList参数的验结果,用到过类似的东西,首先写一个公共函数,将数据组装成一个内存数组: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; 写完公共函数后可以做一个测试了,如果你是较高版本的数据库就这样使用: SQL> SELECT * FROM TABLE(SPLIT('123,321',','));COLUMN_VALUE-------------------------------------------------------------------------------- 123 321如果你是较低版本的数据库,就这样使用: SQL> SELECT * FROM TABLE(CAST(SPLIT('123,321,456', ',') AS MY_TABLE_TYPE)); COLUMN_VALUE-------------------------------------------------------------------------------- 123 321 456 SQL可以动态绑定任何参数进入,只要传入的规格和实际的规格一致即可。上述函数可以任意使用,只要拆开的个数不是太多,对性能影响不算大。
不好意思,补充一下,请LZ在创建函数前,先执行一下这个语句,创建一个数据类型: CREATE OR REPLACE TYPE MY_TABLE_TYPE IS TABLE OF VARCHAR2(8000);
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
看这里!
decode(a,1,b,2,c,3,d,4) 判断如果=a 则值为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))
from (select a.*,b.rn from lhx_tmp a,
(select rownum rn from dual connect by rownum < 5) b) c
order by 1
FROM (SELECT a.card_code,
a.q,
row_number() over(PARTITION BY a.card_code ORDER BY a.q) rn
FROM t_change_lc_comma a) t1
START WITH t1.rn = 1
CONNECT BY t1.card_code = PRIOR t1.card_code
AND t1.rn - 1 = PRIOR t1.rn
GROUP BY t1.card_code;
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;
写完公共函数后可以做一个测试了,如果你是较高版本的数据库就这样使用:
SQL> SELECT * FROM TABLE(SPLIT('123,321',','));COLUMN_VALUE--------------------------------------------------------------------------------
123
321如果你是较低版本的数据库,就这样使用:
SQL> SELECT * FROM TABLE(CAST(SPLIT('123,321,456', ',') AS MY_TABLE_TYPE)); COLUMN_VALUE--------------------------------------------------------------------------------
123
321
456
SQL可以动态绑定任何参数进入,只要传入的规格和实际的规格一致即可。上述函数可以任意使用,只要拆开的个数不是太多,对性能影响不算大。
CREATE OR REPLACE TYPE MY_TABLE_TYPE IS TABLE OF VARCHAR2(8000);