oracle行列轉換-列轉行

在itpub論壇看到一則貼子，行列轉換總結，寫的很全面。現將我的測試結果覆上來，留著日後參考。
附上測試指令碼，有表及表中資料如下

點選(此處)摺疊或開啟

SQL> create table t_col_row(
2 id int,
3 c1 varchar2(10),
4 c2 varchar2(10),
5 c3 varchar2(10));
表已建立。
SQL> insert into t_col_row values(1,'v11','v21','v31');
已建立 1 行。
SQL> insert into t_col_row values(2,'v12','v22',null);
已建立 1 行。
SQL> insert into t_col_row values(3,'v13',null,'v33');
已建立 1 行。
SQL> insert into t_col_row values(4,null,'v24','v34');
已建立 1 行。
SQL> insert into t_col_row values(5,'v15',null,null);
已建立 1 行。
SQL> insert into t_col_row values(6,null,null,'v35');
已建立 1 行。
SQL> insert into t_col_row values(7,null,null,null);
已建立 1 行。
SQL> commit;
提交完成。

查詢t_col_row表中資料如下：

SQL> select * from t_col_row;

        ID C1         C2         C3
---------- ---------- ---------- ----------
         1 v11        v21        v31
         2 v12        v22
         3 v13                   v33
         4            v24        v34
         5 v15
         6                       v35
         7

已選擇7行。

現要將此表資料做列轉行，共有三種方法，如下：
1)UNION ALL
適用範圍：8i,9i,10g及以後版本

點選(此處)摺疊或開啟

SQL> select id,'c1' cn,c1 cv from t_col_row
2 union all
3 select id,'c2' cn,c2 cv from t_col_row
4 union all
5 select id,'c3' cn,c3 cv from t_col_row;
ID CN CV
---------- -- ----------
1 c1 v11
2 c1 v12
3 c1 v13
4 c1
5 c1 v15
6 c1
7 c1
1 c2 v21
2 c2 v22
3 c2
4 c2 v24
ID CN CV
---------- -- ----------
5 c2
6 c2
7 c2
1 c3 v31
2 c3
3 c3 v33
4 c3 v34
5 c3
6 c3 v35
7 c3
已選擇21行。

2)MODEL
適用範圍：10g及以後

點選(此處)摺疊或開啟

SQL> SELECT id, cn, cv FROM t_col_row
2 MODEL
3 RETURN UPDATED ROWS
4 PARTITION BY (ID)
5 DIMENSION BY (0 AS n)
6 MEASURES ('xx' AS cn,'yyy' AS cv,c1,c2,c3)
7 RULES UPSERT ALL
8 (
9 cn[1] = 'c1',
10 cn[2] = 'c2',
11 cn[3] = 'c3',
12 cv[1] = c1[0],
13 cv[2] = c2[0],
14 cv[3] = c3[0]
15 )
16 ORDER BY ID,cn;
ID CN CV
---------- -- ---
1 c1 v11
1 c2 v21
1 c3 v31
2 c1 v12
2 c2 v22
2 c3
3 c1 v13
3 c2
3 c3 v33
4 c1
4 c2 v24
ID CN CV
---------- -- ---
4 c3 v34
5 c1 v15
5 c2
5 c3
6 c1
6 c2
6 c3 v35
7 c1
7 c2
7 c3
已選擇21行。

3)collection
適用範圍：8i,9i,10g及以後版本
要建立一個物件和一個集合：

點選(此處)摺疊或開啟

SQL> create type cv_pair as object(cn varchar2(10),cv varchar2(10));
2 /
型別已建立。
SQL> create type cv_varr as varray(8) of cv_pair;
2 /
型別已建立。
SQL> select id,t.cn as cn,t.cv as cv
2 from t_col_row,
3 table(cv_varr(cv_pair('c1',t_col_row.c1),
4 cv_pair('c2',t_col_row.c2),
5 cv_pair('c3',t_col_row.c3)))t
6 order by 1,2;
ID CN CV
---------- ---------- ----------
1 c1 v11
1 c2 v21
1 c3 v31
2 c1 v12
2 c2 v22
2 c3
3 c1 v13
3 c2
3 c3 v33
4 c1
4 c2 v24
ID CN CV
---------- ---------- ----------
4 c3 v34
5 c1 v15
5 c2
5 c3
6 c1
6 c2
6 c3 v35
7 c1
7 c2
7 c3
已選擇21行。

注：11g之後，又出了個listagg分析函式，更容易實現列轉行，由於本機裝的是10g，無法測試，先附一段網上的程式碼吧。

點選(此處)摺疊或開啟

with temp as(
select 'China' nation ,'Guangzhou' city from dual union all
select 'China' nation ,'Shanghai' city from dual union all
select 'China' nation ,'Beijing' city from dual union all
select 'USA' nation ,'New York' city from dual union all
select 'USA' nation ,'Bostom' city from dual union all
select 'Japan' nation ,'Tokyo' city from dual
)
select nation,listagg(city,',') within GROUP (order by city)
from temp
group by nation

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/21251711/viewspace-1066741/，如需轉載，請註明出處，否則將追究法律責任。

oracle行列轉換-列轉行

相關文章