ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 列變行的SQL語句

列變行的SQL語句

原创 Linux操作系统 作者:jack198409 时间:2007-11-08 00:00:00 0 删除 编辑

多列變一行

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

又是一種裂變行的

一个二维表格:成绩单表格
姓名 语文|数学|外语
张三|80 |78| 77
李四|66 |98| 88
王五|55 |76| 99


其中,姓名|语文|数学|外语 是列名称
通过语句变成:

姓名|课程|份数 ---------列名

张三|语文|80
张三|数学|78
张三|外语|77
李四|语文|88
李四|数学|98
李四|外语|77
********

请问可否实现?有什么好办法吗?真诚求助
是否可以实现
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 課程 分數
---- ---- ----------
王五 外語 99
王五 語文 55
王五 數學 76
李四 外語 88
李四 語文 66
李四 數學 98
張三 外語 77
張三 語文 80
張三 數學 78

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10768286/viewspace-83466/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 行列統計
请登录后发表评论 登录
全部评论

注册时间:2007-12-13

  • 博文量
    135
  • 访问量
    284943