日期:2014-05-17  浏览次数:20830 次

急求oracle把一列拆分成多行显示
如何把一列,拆分成多行
1
2
3
4
5
6
结果
多行:
1 2 3
4 5 6
最好能自定义行数
1 2
3 4
5 6

------解决方案--------------------
SQL code
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;

 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 6

SQL> 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
------解决方案--------------------
select wm_concat(a) from tablet group by ceil(a/n)
n自己确定