将纵向数据列表转化为横向数据字段

转载于：http://dev.firnow.com/course/7_databases/oracle/oraclejs/20100628/213847.html

 

　1 前言
　　在实际编程过程中，往往会遇到比较变态的用户需求。例如：用户需要将不同行的数据，合并起来，显示于字段中的某个列。
　　例如：第一生产线生产：A，B产品；第二生产线生产：C、D产品，在数据库中的记录应为：
　　生产线　产品
　　1　　　 A
　　1　　　 B
　　2　　　 C
　　2　　　 D
　　但是用户希望我们在界面中显示的结果为：
　　生产线　产品
　　1　　　 A、B
　　2　　　 C、D
　　
　　由于FORM不象PB，可以在前台支持交叉表；用户又需求对该数据进行某种处理，所以不能放在REPORT中实现，因此实现此类用户需求，就只能在后台实现了。
　　实现该用户需求，大部份程序员会选择使用临时表，手工插入数据，本文主要论述使用视图实现该需求的方法。
　　当然，使用临时表实现该需求和使用视图实现该需求的执行效率对比，仁者见仁，智者见智，不在本文中研讨。
　　
　　2 实现思路
　　2.1 创建一个表类型，用于将相同生产线不同的产品作为参数进行传递
　　
　　2.2 创建一个函数，将传入的数据表类型参数，转化为字符类型返回
　　
　　2.3 创建一个视图，将纵向的数据转化为横向的数据，以字符方式显示
　　
　　3 实例演练
　　--3.1 创建测试环境
　　--创建数据表类型
　　Create Or Replace Type Tp_TempTable Is Table Of Varchar2(200);
　　/
　　--创建函数，将传入的数据列表转为横向字符
　　Create Or Replace Function FN_GetLineProd　
　　　(P_LineProd In TP_TempTable)　
　　　Return Varchar2
　　Is
　　　L_Str varchar2(4000);
　　　i Number;
　　Begin
　　　For i in 1..P_LineProd.Count Loop
　　　　 If L_Str Is Null Then
　　　　　　L_Str:=P_LineProd(i);
　　　　 Else　
　　　　　 L_Str := L_Str || '、' || P_LineProd(i);
　　　　 End If;
　　　end loop;
　　　Return L_Str;
　　End FN_GetLineProd;
　　/
　　
　　--创建过渡表
　　Drop Table T;
　　create table T (Line number,Prod varchar2(10));
　　insert into T values(1,'A');
　　insert into T values(1,'B');
　　insert into T values(1,'C');
　　insert into T values(2,'D');
　　insert into T values(2,'E');
　　Commit;
　　
　　--3.2 执行查询
　　--查询语句
　　select　
　　　　T0.Line　--生产线编号
　　　　,FN_GetLineProd(　--将同一生产线，不同产品的数据列表作为参数传入自定义函数
　　　　　Cast(　--将嵌套表类型转化为数据表类型
　　　　　　Multiset(Select Prod From T Where T.Line = T0.Line)　--将与最外层查询相同的生产线产品列表，以嵌套表方式返回
　　　　　　　 as TP_TempTable)
　　　　　) Prod
　　　From　
　　　　(Select Distinct Line From T) T0 --得到生产线列表

文章出处：飞诺网(www.firnow.com):http://dev.firnow.com/course/7_databases/oracle/oraclejs/20100628/213847.html