多列變一行

a 表中有三条记录如下：

M N
1 开1
101 开2
10101 开3

如何用一条SQL将a表中的三条数据变成一条插入b表，如下：

M1 N1 M2 N2 M3 N3
1 开1 101 开2 10101 开3

WITH A AS (SELECT 1 M,'開1' N FROM DUAL
UNION
SELECT 101 M,'開2' N FROM DUAL
UNION
SELECT 10101 M,'開3' N FROM DUAL
)
SELECT
MAX(DECODE(C.SEQ, 1, C.M)) AS M1,
MAX(DECODE(C.SEQ, 1, C.N)) AS N1,
MAX(DECODE(C.SEQ, 2, C.M)) AS M2,
MAX(DECODE(C.SEQ, 2, C.N)) AS N2,
MAX(DECODE(C.SEQ, 3, C.M)) AS M3,
MAX(DECODE(C.SEQ, 3, C.N)) AS N3
FROM
(
SELECT A.M,
A.N,
ROW_NUMBER () OVER(PARTITION BY 1 ORDER BY A.M) AS SEQ
FROM A
) C

一列變一行

例2：

如下格式：

Col
aaaa
bbbb
cccc
....
转换后
col
aaaa,bbbb,cccc,....

with a as (select 'aaaa' co from dual
union
select 'bbbb' co from dual
union
select 'cccc' co from dual
)

方法一：
select substr(max(sys_connect_by_path(co,'->')),3) rm
from (select a.*,rownum rn from a)
start with rn=1
connect by rn-1=prior rn

方法二：

create or replace function ff (P_CO VARCHAR2)
return varchar2 is
v_co varchar2(400);
begin
for c1 in (select co from A WHERE CO=P_CO ) LOOP
v_co:=v_co||','||C1.CO;
END LOOP;
RETURN V_CO;
END ;
select MAX(FF(CO) FROM A;

一列變多行

WITH A AS (SELECT 'A' CD FROM DUAL
UNION
SELECT 'B' CD FROM DUAL
UNION
SELECT 'C' CD FROM DUAL
UNION
SELECT 'D' CD FROM DUAL
UNION
SELECT 'E' CD FROM DUAL
UNION
SELECT 'F' CD FROM DUAL
UNION
SELECT 'G' CD FROM DUAL
UNION
SELECT 'H' CD FROM DUAL
UNION
SELECT 'I' CD FROM DUAL
)
select
max(decode(mod(rownum, 5), 1, CD, null)) ID1,
max(decode(mod(rownum, 5), 2, CD, null)) ID2,
max(decode(mod(rownum, 5), 3, CD, null)) ID3,
max(decode(mod(rownum, 5), 4, CD, null)) ID4,
max(decode(mod(rownum, 5), 0, CD, null)) ID5
from a
group by ceil(rownum / 5)
ID1 ID2 ID3 ID4 ID5
--- --- --- --- ---
A B C D E
F G H I

SQL> WITH A AS (SELECT 1 CD1,0 CD2 FROM DUAL
2 UNION
3 SELECT 2 CD1,0 CD2 FROM DUAL
4 UNION
5 SELECT 3 CD1,0 CD2 FROM DUAL
6 UNION
7 SELECT 4 CD1,0 CD2 FROM DUAL
8 UNION
9 SELECT 5 CD1,0 CD2 FROM DUAL
10 )
11 SELECT * FROM (SELECT MAX(DECODE(RN,1,CD1,NULL)) ID1,
12 MAX(DECODE(RN,2,CD1,NULL)) ID2,
13 MAX(DECODE(RN,3,CD1,NULL)) ID3,
14 MAX(DECODE(RN,4,CD1,NULL)) ID4,
15 MAX(DECODE(RN,5,CD1,NULL)) ID5
16 FROM (SELECT CD1,CD2,ROW_NUMBER()OVER( ORDER BY CD1) RN FROM A)
17 UNION
18 SELECT MAX(DECODE(RN,1,CD2,NULL)) ID1,
19 MAX(DECODE(RN,2,CD2,NULL)) ID2,
20 MAX(DECODE(RN,3,CD2,NULL)) ID3,
21 MAX(DECODE(RN,4,CD2,NULL)) ID4,
22 MAX(DECODE(RN,5,CD2,NULL)) ID5
23 FROM (SELECT CD1,CD2,ROW_NUMBER()OVER( ORDER BY CD1) RN FROM A))
24 ORDER BY ID1 DESC
25 ;

ID1 ID2 ID3 ID4 ID5
---------- ---------- ---------- ---------- ----------
1 2 3 4 5
0 0 0 0 0

一行變一列（或是多列變一列）

现在想把C拆分成多行 比如
A B C D
13 42 a;b;c 20071031
拆分成

A B C D
13 42 a 20071031
13 42 b 20071031
13 42 c 20071031

SQL> WITH A AS (SELECT 13 A,42 B ,'a;b;c' C,20071031 D FROM DUAL)
2 select A,B,SUBSTR(C,1,1) ID1,D FROM A
3 UNION
4 select A,B,SUBSTR(C,3,1) ID1,D FROM A
5 UNION
6 select A,B,SUBSTR(C,5,1) ID1,D FROM A
7 ;

A B ID1 D
---------- ---------- --- ----------
13 42 a 20071031
13 42 b 20071031
13 42 c 20071031

又是一種裂變行的