# 列變行的SQL語句

a 表中有三条记录如下：

M N
1 开1
101 开2
10101 开3

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

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)
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

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

********

SQL> with a as (select '張三' name,80 yuwen,78 shuxue,77 waiyu from dual
2 union
3 select '李四' name,66 yuwen,98 shuxue,88 waiyu from dual
4 union
5 select '王五' name,55 yuwen,76 shuxue,99 waiyu from dual
6 )
7 select name,'語文' 課程,yuwen 分數 from a
8 union
9 select name,'數學', shuxue from a
10 union
11 select name,'外語', waiyu from a
12 ;

NAME 課程 分數
---- ---- ----------

• 博文量
135
• 访问量
289531