SELECT a,b FROM ( SELECT decode(col,1,rn) a,lead(decode(col,0,rn))over(ORDER BY 1) b FROM ( SELECT rn, MOD(rn,2) col FROM (SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM<7) ) ) WHERE a IS NOT NULL AND b IS NOT NULL--2列的情况: 1 2 3 4 5 6
--创建环境 create table tmpTable as with tmp as ( select 1 as col1 from dual union select 2 from dual union select 3 from dual union select 4 from dual union select 5 from dual union select 6 from dual ) select * from tmp;--创建存储过程 CREATE OR REPLACE PROCEDURE TEST_COL_TO_ROW ( colNum IN INT, user_cursor out sys_refcursor ) AS V_SQL VARCHAR2(8000);
BEGIN V_SQL := 'SELECT ';
FOR X IN 1..colNum LOOP V_SQL := V_SQL || 'SUM(DECODE(mod(rownum-1,'||to_char(colNum)||'),'||to_char(x-1)||',col1,0)) COL'||to_char(x) || ',' ; END LOOP;
V_SQL := RTRIM(V_SQL,','); V_SQL := V_SQL || ' from tmpTable group by trunc((rownum-1)/'||to_char(colNum)||') order by trunc((rownum-1)/'||to_char(colNum)||')';
--DBMS_OUTPUT.PUT_LINE(V_SQL); OPEN user_cursor FOR V_SQL; --EXECUTE IMMEDIATE V_SQL;
select wm_concat(a) from tablet group by ceil(a/n) n自己确定
SQL> with t as 2 (select level a from dual connect by level<=100), 3 s as (select a,rownum rn from t order by a) 4 select wm_concat(a) from s group by ceil(rn/2);
FROM
(
SELECT decode(col,1,rn) a,lead(decode(col,0,rn))over(ORDER BY 1) b
FROM
(
SELECT rn, MOD(rn,2) col
FROM
(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM<7)
)
) WHERE a IS NOT NULL AND b IS NOT NULL--2列的情况:
1 2
3 4
5 6
create table tmpTable as
with tmp as (
select 1 as col1 from dual
union
select 2 from dual
union
select 3 from dual
union
select 4 from dual
union
select 5 from dual
union
select 6 from dual
)
select * from tmp;--创建存储过程
CREATE OR REPLACE PROCEDURE TEST_COL_TO_ROW
(
colNum IN INT,
user_cursor out sys_refcursor
)
AS
V_SQL VARCHAR2(8000);
BEGIN
V_SQL := 'SELECT ';
FOR X IN 1..colNum LOOP
V_SQL := V_SQL || 'SUM(DECODE(mod(rownum-1,'||to_char(colNum)||'),'||to_char(x-1)||',col1,0)) COL'||to_char(x) || ',' ;
END LOOP;
V_SQL := RTRIM(V_SQL,',');
V_SQL := V_SQL || ' from tmpTable group by trunc((rownum-1)/'||to_char(colNum)||') order by trunc((rownum-1)/'||to_char(colNum)||')';
--DBMS_OUTPUT.PUT_LINE(V_SQL);
OPEN user_cursor FOR V_SQL;
--EXECUTE IMMEDIATE V_SQL;
END;
--SQLPLUS中执行SQL> variable v_c refcursor;
SQL> exec TEST_COL_TO_ROW(2,:v_c);PL/SQL 过程已成功完成。SQL> print :v_c; COL1 COL2
---------- ----------
1 2
3 4
5 6SQL> variable v_c refcursor;
SQL> exec TEST_COL_TO_ROW(3,:v_c);PL/SQL 过程已成功完成。SQL> print :v_c; COL1 COL2 COL3
---------- ---------- ----------
1 2 3
4 5 6
当然,也可以在PL/SQL中实现
但要实现为一个SQL语句不太容易。
n自己确定
2 (select level a from dual connect by level<=100),
3 s as (select a,rownum rn from t order by a)
4 select wm_concat(a) from s group by ceil(rn/2);
WM_CONCAT(A)
--------------------------------------------------------------------------------
1,2
3,4
5,6
7,8
9,10
11,12
13,14
15,16
17,18
19,20
21,22
23,24
25,26
27,28
29,30
31,32
33,34
35,36
37,38
39,40
WM_CONCAT(A)
--------------------------------------------------------------------------------
41,42
43,44
45,46
47,48
49,50
51,52
53,54
55,56
57,58
59,60
61,62
63,64
65,66
67,68
69,70
71,72
73,74
75,76
77,78
79,80
81,82
WM_CONCAT(A)
--------------------------------------------------------------------------------
83,84
85,86
87,88
89,90
91,92
93,94
95,96
97,98
99,100
50 rows selected
SQL>