Oracle SQL行列转换应用实例一则

现有如下两张表

com_tree

ID	LEVEL1	LEVEL2	LEVEL3	LEVEL4	LEVEL5
1	001	001	001	001	001
2	001	002	002	002	002
3	001	003	003	003	003
4	001	004	004	004	004
5	001	390	007	007	007
6	001	101	101	101	101
7	001	201	101	101	101
8	001	101	102	102	102

 

 

com_desc

ID	CODE	DESCPTIONS
1	001	股份公司
2	002	工程物流事业部
3	003	特种物流事业部
4	004	股份船代公司
5	005	中瑞检验
6	006	山东力神
7	007	重庆物流
8	008	新陆桥

 

需要实现如下图的需求：

 

ID	LEVEL1	LEVEL1_DESC	LEVEL2	LEVEL2_DESC	LEVEL3	LEVEL3_DESC	LEVEL4	LEVEL4_DESC	LEVEL5	LEVEL5_DESC
1	001	股份公司	001	股份公司	001	股份公司	001	股份公司	001	股份公司
6	001	股份公司
2	001	股份公司	002	工程物流事业部	002	工程物流事业部	002	工程物流事业部	002	工程物流事业部
4	001	股份公司	004	股份船代公司	004	股份船代公司	004	股份船代公司	004	股份船代公司
5	001	股份公司	007	重庆物流	007	重庆物流	007	重庆物流
8	001	股份公司
3	001	股份公司	003	特种物流事业部	003	特种物流事业部	003	特种物流事业部	003	特种物流事业部

 

 

怎么办呢？很多人拿到之后一想法就是写一get_desc的函数，然后调用多次就ok了。这种办法虽然简单易行，但是很明显效率不行(考虑recursive calls吧)

有没有其它办法呢？当然有了，我们把com_tree里的列变成行，然后和com_desc关联，最后在把结果变成列不就OK了么？

 

这里我们需要用到列转行以及行转列，不会的同学们请自行搜索吧！

 

本次我们不采用经典写法了，用用oracle 11g提供的pivot及unpivot函数吧。

 

第一步，对com_tree进行列转行并关联com_desc，使用unpivot函数

 

代码如下：

select a.id,a.lvl,a.code as code1,b.descptions from 
(select * from com_tree 
unpivot
(
code for lvl in (level1 as 1,level2 as 2,level3 as 3,level4 as 4,level5 as 5)
)) a,
com_desc b
where a.code=b.code

注：in中的level请自行根据自己的情况进行添加，为了便于拓展可以考虑预留几个level

 

第二步，对第一步的结果集进行行转列即可

 

select * from 
(
select a.id,a.lvl,a.code as code1,b.descptions from 
(select * from com_tree 
unpivot
(
code for lvl in (level1 as 1,level2 as 2,level3 as 3,level4 as 4,level5 as 5)
)) a,
com_desc b
where a.code=b.code
)
pivot(
max(code1),max(descptions) as "DESC" for lvl in (1 as level1,2 as level2,3 as level3,4 as level4,5 as level5)
)

 

至此大功告成！

 

 

最后附测试表的脚本：

create table COM_DESC
(
  id         INTEGER,
  code       VARCHAR2(10),
  descptions VARCHAR2(30)
);

create table COM_TREE
(
  id     INTEGER,
  level1 VARCHAR2(10),
  level2 VARCHAR2(10),
  level3 VARCHAR2(10),
  level4 VARCHAR2(10),
  level5 VARCHAR2(10)
)
;

insert into COM_DESC (id, code, descptions)
values (1, '001', '股份公司');
insert into COM_DESC (id, code, descptions)
values (2, '002', '工程物流事业部');
insert into COM_DESC (id, code, descptions)
values (3, '003', '特种物流事业部');
insert into COM_DESC (id, code, descptions)
values (4, '004', '股份船代公司');
insert into COM_DESC (id, code, descptions)
values (5, '005', '中瑞检验');
insert into COM_DESC (id, code, descptions)
values (6, '006', '山东力神');
insert into COM_DESC (id, code, descptions)
values (7, '007', '重庆物流');
insert into COM_DESC (id, code, descptions)
values (8, '008', '新陆桥');

insert into COM_TREE (id, level1, level2, level3, level4, level5)
values (1, '001', '001', '001', '001', '001');
insert into COM_TREE (id, level1, level2, level3, level4, level5)
values (2, '001', '002', '002', '002', '002');
insert into COM_TREE (id, level1, level2, level3, level4, level5)
values (3, '001', '003', '003', '003', '003');
insert into COM_TREE (id, level1, level2, level3, level4, level5)
values (4, '001', '004', '004', '004', '004');
insert into COM_TREE (id, level1, level2, level3, level4, level5)
values (5, '001', '390', '007', '007', '007');
insert into COM_TREE (id, level1, level2, level3, level4, level5)
values (6, '001', '101', '101', '101', '101');
insert into COM_TREE (id, level1, level2, level3, level4, level5)
values (7, '001', '201', '101', '101', '101');
insert into COM_TREE (id, level1, level2, level3, level4, level5)
values (8, '001', '101', '102', '102', '102');
commit;