在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博客

oracle行列转换-列转行

最新文章