环境oracle 10g
工作关系,常做些行转列报表,报表通常不是在大数据集合上处理.
所以写了个过程.
本过程比较适合在于需要动态输出报表的地方,例如web中.
不是很完美,但已经可以解决绝大部分的问题.
create or replace function func_RowToCol(
viewName Varchar2,
grpCols Varchar2,
colCol Varchar2,
valueCol Varchar2,
fillEmptyWithZero Number:=1,
rowOrder Varchar2:='',
colOrder Varchar2:='',
rowOrderinGrp Integer:=1,
colOrderStyle Varchar2:=' asc ',
fillValue Varchar2:=''
) return varchar2
Is
/*****************************************************************************************************
名称:func_RowToCol
参数说明:
viewName 视图名称,实际上可以是数据库的表格名称,视图名称,也可以是SQL语句.
grpCols 需要分组的列,以格式 col1,col2..coln传入,其中n是大于0的整数
colCol 由行转为列的那个列
valueCol 行转列后,依然作为值填充的那个列,只能是一个列
--viewIsSql 视图是否是sql语句,如果是则传入1,反之传入2,默认是1(是sql)
fillEmptyWithZero 用0来填充空值,默认空值依然保留空值.如果是1,则只对valueCol为数值类型的有效.
rowOrder 结果默认的排序语句,如果有,则使用这个，这个是对结果的行排序
colOrder 对转成的列进行排序的依据.
rowOrderinGrp 行的排序列是否在分组列（grpcols)中，0 表示不是，1表示是，默认是在分组列中。
colOrderStyle 这个参数说明了列的排序方式
fillValue 填充值，如果非空,且fillEmptyWithZero=1，则用.
举例:有一个表格EmpSalary(SalMonth number,EmpName varchar2(20),salary number) 其中
salMonth,EmpName组成唯一约束
假设有以下数据:
SALMONTH EMPNAME SALARY
---------- -------------------- ----------
200801 lzf 8000
200801 wth 8000
200801 lxl 7000
200801 fjl 8000
200801 wcl 40000
200802 lzf 9000
200802 wth 8000
....
现在需要按照这样的格式输出
salaryMonth lzf wth lxl fjl wcl
200801 8000 8000 7000 8000 40000
200801 9000 8000
那么参数应该这样传递func_RowToCol('empsalary','','salarymonth','empname','salary',0,1);
输出:
如果成功,则返回一个基于tempdata_manycols的查询sql字符串
如果失败,则返回空值.
注意事项:
本函数是基于一个叫tempdata_manyCols的全局临时表处理的.
并且有以下假设:
1)固定列加动态列不超过300列
2)原来的一个列(只能有一个列)作为行内容填充的新形成后的表格中.
3)只能处理数字或者字符的信息,如果是字符,不能超过2000个.但本函数的colCol的值不应该超过30个B.
因为太长的话,行转列就没有意义了(根本没有办法看),同时oracle也不支持超过30B的列名
4)********************************不建议的事情***********************************************
*不建议对一个巨大的记录集合进行行专列操作,否则可能效率之低下是难于想象
*盖因为行转列通常用于编写报表之用.
*也严重不建议,传入的视图是基于一个很耗时的复杂查询
*最后,如果您的sql大于32K左右,本过程无能为力!
*******************************************************************************************
5)严重警告:数据源必须有数据,其次分组列应该都有数据,
6)不接受需要把数据聚集之后再显示的数据,最好自行先聚集.
修改记录:
------------------------------------------------------------
2009-03-11 lzf 新增
2009-03-12 lzf 完成初稿,可以在简单代码上测试成功.
2009-03-27 lzf 增加一个控制转换列输出的功能 colOrder
2009-04-01 lzf 修改，以便更完善
2009-04-07 lzf 修改，增加了列排序的方式,为了节约时间，不再调整参数顺序.
增加了一个填充值，以便按照要求来填充需要的内容.
*****************************************************************************************************
*/
Pragma Autonomous_Transaction;
vResultsql varchar2(32767):='';
-----处理临时数据的变量
vDatas type_str_arrs:=type_str_arrs();
vDealRows pls_integer:=0;
----最终存放数据的地方
vColNames type_str_arr:=type_str_arr(); --列名数组
vColAmount pls_integer:=0; --列的个数
vRowNames type_str_arr:=type_str_arr(); --行的内容
vGrpColAmount pls_integer:=0; --分组字段的个数,即grpCols的字段个数.
--vStarColPos pls_integer:=1; --返回的起点列标号，默认是1，但是如果行分组列不在其中，则从2开始
vSortAmount pls_integer:=0;
vIntoSqls Varchar2(32767):=''; --用于存储插入到tempdata_manycols的into脚本
vJoinCols Varchar2(32767):=''; --插入和分组的字段
vOrderCols Varchar2(32767):=''; --排序的字段
vOrderCols2 Varchar2(32767):='';
vRecordAmount pls_integer:=0; --原始数据记录数
--填充值
vFillValue Varchar2(1000):='';
v_sSql Varchar2(32767);
Function getGrpColAmount(pGrpCols In Varchar2,vRows In Out type_str_arr) Return Pls_Integer
Is
/*
本函数的作用:计算分组字段的个数
*/
Begin
--一个自定义的函数,用于把用特定符号隔开的字符串分解到一个字符串数组中.
pkg_bit.SpilitStr(pGrpcols,',',vRows);
Return vRows.Count;
End;
Function getJoinSql(pGrpcols In Varchar2) Return Varchar2
Is
/*
本函数的作用:返回join中的条件,已经假定,两个表一定是x,y
*/
vCols type_str_arr:=type_str_arr();
vResult Varchar2(32767):='';
vColName Varchar2(30):='' ; --列名
Begin
--一个自定义的函数,用于把用特定符号隔开的字符串分解到一个字符串数组中.
pkg_bit.SpilitStr(pGrpcols,',',vCols);
For i In 1..vCols.Count Loop
vColName:=vCols(i);
If i=1 Then
vResult:='y.'||vColName||'=x.'||vColName;
Else
vResult:=vResult||' and y.'||vColName||'=x.'||vColName;
End If;
End Loop;
Return vResult;
End;
Function getIntoSql(pGrpCols In Varchar2,pColCol In Varchar2:='',pRowOrderCol In Varchar2:='') Return Varchar2
Is
/*
本函数的作用:根据分组字段和转列来决定插入到临时表中所需要的子句sql
pColCol目前是没有什么意义存在的.
*/
vTempStr Varchar2(32767):=pGrpCols||','||Case When pColCol Is Not Null Then pColCol||',' Else '' End;
vPos Pls_Integer;
vAmount Pls_Integer:=0;
vResult Varchar2(32767):='';
Begin
--行排序列放在首位.
vTempstr:=Case When pRowOrderCol Is Not Null Then pRowOrderCol||',' Else '' End ||vTempstr;
vPos:=instr(vTempStr,',');
While vpos>0 Loop
vAmount:=vAmount+1;
--组成输出字段
If vAmount=1 Then
vResult:='C'||to_char(vAmount);
Else
vResult:=vResult||',C'||to_char(vAmount);
End If;
vTempStr:=substr(vTempStr,vpos+1);
vPos:=instr(vTempStr,',');
End Loop;
Return vResult;
End;
Function getOrderSql(pGrpCols In Varchar2) Return Varchar
Is
/*
本函数的作用:组成排序字段.
*/
Begin
Return getIntoSql(pGrpCols,'');
End;
Begin
--0)获得分组字段的个数
vGrpColAmount:=getGrpColAmount(grpCols,vRowNames);
--1)获得转为列之后的列名,列个数
v_sSql:='
Select Distinct '||colCol||' from ( '||viewName||' ) order by '||colCol;
Execute Immediate v_sSql Bulk Collect Into vColNames;
vColAmount:=vColNames.Count;
--2)把数据填充到临时数组表中
If colOrder Is Not Null Then --是用这个.
vOrderCols2:=' x.'||replace(grpCols,',',',x.')||',x.'||colOrder||colOrderStyle;
Else
vOrderCols2:= ' x.'||replace(grpCols,',',',x.')||',x.'||colCol||colOrderStyle;
End If;
--计算填充内容
If fillEmptyWithZero=1 And fillValue Is Not Null Then
vFillValue:=fillValue;
Elsif fillEmptyWithZero=1 And fillValue Is Null Then
vFillValue:='0';
Elsif fillEmptyWithZero=0 Then
vFillValue:='';
End If;
Execute Immediate 'truncate table tempdata_manyCols';
vJoinCols:=getJoinSql(grpCols);
v_sSql:='
select type_str_arr('||Case When rowOrderinGrp=0 Then 'x.'||rowOrder||','
Else '' End||'x.'||replace(grpCols,',',',x.')||',x.'||colCol||','||
Case When vFillValue Is Not Null Then 'nvl('||valueCol||','''||vFillValue||''') '
Else valueCol
End ||') from
(
select a.*,b.* from
(Select Distinct '||colCol||Case When colOrder Is Null Then '' Else ','||colOrder End||' from ('||viewName||')) a,
(select distinct '||grpCols||Case When rowOrderinGrp=0 Then ','||rowOrder Else '' End||' from ('||viewName||')) b
) x
left join ('||viewName||') y on '||vJoinCols||' and y.'||colCol||'=x.'||colCol||'
order by '||vOrderCols2;
Execute Immediate v_sSql Bulk Collect Into vDatas;
Commit;
--3)通过矩阵转换,读取vDatas的内容,插入到tempdata_manycols
vRecordAmount:=vDatas.Count;
--设置插入列的SQL
If rowOrderinGrp=1 Then --如果排序行的列在分组列中，那么就仅仅使用分组列即可.
vIntoSqls:=getIntoSql(grpCols);
Else --如果行的排序列不在分组列中，那么就再多插入一个列.
vIntoSqls:=getIntoSql(grpCols,'',rowOrder);
vSortAmount:=1;
End If;
/*组合sql
*/
vDealRows:=0;
For i In 1..vRecordAmount Loop --每一行
If Mod(i,vColAmount)=0 Then --当读取到足够一行数据的时候
vDealRows:=vDealRows+1;
If vDealRows=1 Then --为了不至于搞错顺序,第一次需要把转列内容填到
--vColNames,也就是例子中的 lzf,wth...等部分
--同时,组合成真正的intosql
For j In 1 ..vColAmount Loop
--vsortAmount表示的是行排序列的个数.
vColNames(j):=vDatas(j)(vGrpColAmount+vSortAmount+1);
--dbms_output.put(lpad( vColNames(j-(vDealRows-1)*vColAmount),20,' '));
vIntoSqls:=vIntoSqls||',C'||to_char(vGrpColAmount+vSortAmount+j);
End Loop;
End If;
v_sSql:='';
For x In 1..vGrpColAmount+vSortAmount Loop --设置值部分分组部分的内容和排序部分内容
v_sSql:=v_sSql||Case x When 1 Then '' Else ',' End||''''||vDatas(i)(x)||'''';
End Loop;
--读取值部分填充值的信息
--真正的值的位置=分组字段个数＋排序列个数＋2
For j In (vDealRows-1)*vColAmount+1..vDealRows*vColAmount Loop
v_sSql:=v_sSql||','||Case When vDatas(j)(vGrpColAmount+vSortAmount+ 2) Is Null Then 'null'
Else ''''||vDatas(j)(vGrpColAmount+vSortAmount+2)||''''
End;
--dbms_output.put(lpad( vDatas(j)(vGrpColAmount+2),20,' '));
End Loop;
--dbms_output.put_line('');
--组合成最后的SQL
v_sSql:=' insert into tempdata_manycols('||vIntoSqls||') values('
||v_sSql||')';
--dbms_output.put_line(v_sSql);
Execute Immediate v_sSql;
End If;
End Loop;
Commit;
--4)形成返回的sql
/*
关键在于知道列名:=分组名称+行转列
*/
/* If rowOrder Is Not Null And rowOrderinGrp=0 Then
vStarColPos:=2;
End If;*/
vResultsql:='select ';
--分别是排序列，和转换列
For i In 1..vGrpColAmount+vColAmount Loop
If i<=vgrpcolamount Then --前一部分的字段别名是分组字段名称,是外部传入的.
vResultsql:=vResultsql||'
'||(Case i When 1 Then '' Else ',' End)||'C'||(i+vSortAmount)||' as '||vRowNames(i);
Else --后面一部分则是由原来行转过来的列的别名.
vResultsql:=vResultsql||'
'||(Case i When 1 Then '' Else ',' End)||'C'||(i+vSortAmount)||' as "'||vColNames(i-vGrpColAmount)||'"';
End If;
End Loop;
If rowOrder Is Null Then
vOrderCols:=getordersql(grpCols);
Else
--vOrderCols:=rowOrder;
--默认只有一个的情况下.正确的情况，应该是另外处理。
vOrderCols:=' c1 ';
End If;
vResultsql:=vResultsql||'
from tempdata_manycols
order by '||vOrderCols;
return(vResultsql);
Exception
When Others Then
Rollback;
Return '';
end func_RowToCol;
测试如下:
SQL> select * from empsalary;
SALMONTH EMPNAME SALARY COUNTRY
---------- -------------------- ---------- --------------------
200801 lzf 8000 中国
200801 wth 8000 美国
200801 lxl 7000 日本
200801 fjl 7000 巴基斯坦
200801 wcl 40000 美国
200802 lzf 9000 中国
200802 wth 8000 美国
200802 lxl 8500 日本
8 rows selected
SQL> Select func_RowToCol(' empsalary ','salmonth,country','empname','salary') From dual;
FUNC_ROWTOCOL('EMPSALARY','SAL
--------------------------------------------------------------------------------
select
C1 as salmonth
,C2 as country
,C3 as "fjl"
,C4 as "lxl"
,C5 as "lzf"
,C6 as "wcl"
,C7 as "wth"
from tempdata_manycols
order by C1,C2
SQL> select
2 cast(C1 As Varchar2(10)) as salmonth
3 ,cast(C2 As Varchar2(10)) as country
4 ,cast(C3 As Varchar2(10)) as "fjl"
5 ,cast(C4 As Varchar2(10)) as "lxl"
6 ,cast(C5 As Varchar2(10)) as "lzf"
7 ,cast(C6 As Varchar2(10)) as "wcl"
8 ,cast(C7 As Varchar2(10)) as "wth"
9 from tempdata_manycols
10 order by C1,C2
11 /
SALMONTH COUNTRY fjl lxl lzf wcl wth
---------- ---------- ---------- ---------- ---------- ---------- ----------
200801 巴基斯坦 7000 0 0 0 0
200801 美国 0 0 0 40000 8000
200801 日本 0 7000 0 0 0
200801 中国 0 0 8000 0 0
200802 美国 0 0 0 0 8000
200802 日本 0 8500 0 0 0
200802 中国 0 0 9000 0 0
7 rows selected
工作关系,常做些行转列报表,报表通常不是在大数据集合上处理.
所以写了个过程.
本过程比较适合在于需要动态输出报表的地方,例如web中.
不是很完美,但已经可以解决绝大部分的问题.
create or replace function func_RowToCol(
viewName Varchar2,
grpCols Varchar2,
colCol Varchar2,
valueCol Varchar2,
fillEmptyWithZero Number:=1,
rowOrder Varchar2:='',
colOrder Varchar2:='',
rowOrderinGrp Integer:=1,
colOrderStyle Varchar2:=' asc ',
fillValue Varchar2:=''
) return varchar2
Is
/*****************************************************************************************************
名称:func_RowToCol
参数说明:
viewName 视图名称,实际上可以是数据库的表格名称,视图名称,也可以是SQL语句.
grpCols 需要分组的列,以格式 col1,col2..coln传入,其中n是大于0的整数
colCol 由行转为列的那个列
valueCol 行转列后,依然作为值填充的那个列,只能是一个列
--viewIsSql 视图是否是sql语句,如果是则传入1,反之传入2,默认是1(是sql)
fillEmptyWithZero 用0来填充空值,默认空值依然保留空值.如果是1,则只对valueCol为数值类型的有效.
rowOrder 结果默认的排序语句,如果有,则使用这个，这个是对结果的行排序
colOrder 对转成的列进行排序的依据.
rowOrderinGrp 行的排序列是否在分组列（grpcols)中，0 表示不是，1表示是，默认是在分组列中。
colOrderStyle 这个参数说明了列的排序方式
fillValue 填充值，如果非空,且fillEmptyWithZero=1，则用.
举例:有一个表格EmpSalary(SalMonth number,EmpName varchar2(20),salary number) 其中
salMonth,EmpName组成唯一约束
假设有以下数据:
SALMONTH EMPNAME SALARY
---------- -------------------- ----------
200801 lzf 8000
200801 wth 8000
200801 lxl 7000
200801 fjl 8000
200801 wcl 40000
200802 lzf 9000
200802 wth 8000
....
现在需要按照这样的格式输出
salaryMonth lzf wth lxl fjl wcl
200801 8000 8000 7000 8000 40000
200801 9000 8000
那么参数应该这样传递func_RowToCol('empsalary','','salarymonth','empname','salary',0,1);
输出:
如果成功,则返回一个基于tempdata_manycols的查询sql字符串
如果失败,则返回空值.
注意事项:
本函数是基于一个叫tempdata_manyCols的全局临时表处理的.
并且有以下假设:
1)固定列加动态列不超过300列
2)原来的一个列(只能有一个列)作为行内容填充的新形成后的表格中.
3)只能处理数字或者字符的信息,如果是字符,不能超过2000个.但本函数的colCol的值不应该超过30个B.
因为太长的话,行转列就没有意义了(根本没有办法看),同时oracle也不支持超过30B的列名
4)********************************不建议的事情***********************************************
*不建议对一个巨大的记录集合进行行专列操作,否则可能效率之低下是难于想象
*盖因为行转列通常用于编写报表之用.
*也严重不建议,传入的视图是基于一个很耗时的复杂查询
*最后,如果您的sql大于32K左右,本过程无能为力!
*******************************************************************************************
5)严重警告:数据源必须有数据,其次分组列应该都有数据,
6)不接受需要把数据聚集之后再显示的数据,最好自行先聚集.
修改记录:
------------------------------------------------------------
2009-03-11 lzf 新增
2009-03-12 lzf 完成初稿,可以在简单代码上测试成功.
2009-03-27 lzf 增加一个控制转换列输出的功能 colOrder
2009-04-01 lzf 修改，以便更完善
2009-04-07 lzf 修改，增加了列排序的方式,为了节约时间，不再调整参数顺序.
增加了一个填充值，以便按照要求来填充需要的内容.
*****************************************************************************************************
*/
Pragma Autonomous_Transaction;
vResultsql varchar2(32767):='';
-----处理临时数据的变量
vDatas type_str_arrs:=type_str_arrs();
vDealRows pls_integer:=0;
----最终存放数据的地方
vColNames type_str_arr:=type_str_arr(); --列名数组
vColAmount pls_integer:=0; --列的个数
vRowNames type_str_arr:=type_str_arr(); --行的内容
vGrpColAmount pls_integer:=0; --分组字段的个数,即grpCols的字段个数.
--vStarColPos pls_integer:=1; --返回的起点列标号，默认是1，但是如果行分组列不在其中，则从2开始
vSortAmount pls_integer:=0;
vIntoSqls Varchar2(32767):=''; --用于存储插入到tempdata_manycols的into脚本
vJoinCols Varchar2(32767):=''; --插入和分组的字段
vOrderCols Varchar2(32767):=''; --排序的字段
vOrderCols2 Varchar2(32767):='';
vRecordAmount pls_integer:=0; --原始数据记录数
--填充值
vFillValue Varchar2(1000):='';
v_sSql Varchar2(32767);
Function getGrpColAmount(pGrpCols In Varchar2,vRows In Out type_str_arr) Return Pls_Integer
Is
/*
本函数的作用:计算分组字段的个数
*/
Begin
--一个自定义的函数,用于把用特定符号隔开的字符串分解到一个字符串数组中.
pkg_bit.SpilitStr(pGrpcols,',',vRows);
Return vRows.Count;
End;
Function getJoinSql(pGrpcols In Varchar2) Return Varchar2
Is
/*
本函数的作用:返回join中的条件,已经假定,两个表一定是x,y
*/
vCols type_str_arr:=type_str_arr();
vResult Varchar2(32767):='';
vColName Varchar2(30):='' ; --列名
Begin
--一个自定义的函数,用于把用特定符号隔开的字符串分解到一个字符串数组中.
pkg_bit.SpilitStr(pGrpcols,',',vCols);
For i In 1..vCols.Count Loop
vColName:=vCols(i);
If i=1 Then
vResult:='y.'||vColName||'=x.'||vColName;
Else
vResult:=vResult||' and y.'||vColName||'=x.'||vColName;
End If;
End Loop;
Return vResult;
End;
Function getIntoSql(pGrpCols In Varchar2,pColCol In Varchar2:='',pRowOrderCol In Varchar2:='') Return Varchar2
Is
/*
本函数的作用:根据分组字段和转列来决定插入到临时表中所需要的子句sql
pColCol目前是没有什么意义存在的.
*/
vTempStr Varchar2(32767):=pGrpCols||','||Case When pColCol Is Not Null Then pColCol||',' Else '' End;
vPos Pls_Integer;
vAmount Pls_Integer:=0;
vResult Varchar2(32767):='';
Begin
--行排序列放在首位.
vTempstr:=Case When pRowOrderCol Is Not Null Then pRowOrderCol||',' Else '' End ||vTempstr;
vPos:=instr(vTempStr,',');
While vpos>0 Loop
vAmount:=vAmount+1;
--组成输出字段
If vAmount=1 Then
vResult:='C'||to_char(vAmount);
Else
vResult:=vResult||',C'||to_char(vAmount);
End If;
vTempStr:=substr(vTempStr,vpos+1);
vPos:=instr(vTempStr,',');
End Loop;
Return vResult;
End;
Function getOrderSql(pGrpCols In Varchar2) Return Varchar
Is
/*
本函数的作用:组成排序字段.
*/
Begin
Return getIntoSql(pGrpCols,'');
End;
Begin
--0)获得分组字段的个数
vGrpColAmount:=getGrpColAmount(grpCols,vRowNames);
--1)获得转为列之后的列名,列个数
v_sSql:='
Select Distinct '||colCol||' from ( '||viewName||' ) order by '||colCol;
Execute Immediate v_sSql Bulk Collect Into vColNames;
vColAmount:=vColNames.Count;
--2)把数据填充到临时数组表中
If colOrder Is Not Null Then --是用这个.
vOrderCols2:=' x.'||replace(grpCols,',',',x.')||',x.'||colOrder||colOrderStyle;
Else
vOrderCols2:= ' x.'||replace(grpCols,',',',x.')||',x.'||colCol||colOrderStyle;
End If;
--计算填充内容
If fillEmptyWithZero=1 And fillValue Is Not Null Then
vFillValue:=fillValue;
Elsif fillEmptyWithZero=1 And fillValue Is Null Then
vFillValue:='0';
Elsif fillEmptyWithZero=0 Then
vFillValue:='';
End If;
Execute Immediate 'truncate table tempdata_manyCols';
vJoinCols:=getJoinSql(grpCols);
v_sSql:='
select type_str_arr('||Case When rowOrderinGrp=0 Then 'x.'||rowOrder||','
Else '' End||'x.'||replace(grpCols,',',',x.')||',x.'||colCol||','||
Case When vFillValue Is Not Null Then 'nvl('||valueCol||','''||vFillValue||''') '
Else valueCol
End ||') from
(
select a.*,b.* from
(Select Distinct '||colCol||Case When colOrder Is Null Then '' Else ','||colOrder End||' from ('||viewName||')) a,
(select distinct '||grpCols||Case When rowOrderinGrp=0 Then ','||rowOrder Else '' End||' from ('||viewName||')) b
) x
left join ('||viewName||') y on '||vJoinCols||' and y.'||colCol||'=x.'||colCol||'
order by '||vOrderCols2;
Execute Immediate v_sSql Bulk Collect Into vDatas;
Commit;
--3)通过矩阵转换,读取vDatas的内容,插入到tempdata_manycols
vRecordAmount:=vDatas.Count;
--设置插入列的SQL
If rowOrderinGrp=1 Then --如果排序行的列在分组列中，那么就仅仅使用分组列即可.
vIntoSqls:=getIntoSql(grpCols);
Else --如果行的排序列不在分组列中，那么就再多插入一个列.
vIntoSqls:=getIntoSql(grpCols,'',rowOrder);
vSortAmount:=1;
End If;
/*组合sql
*/
vDealRows:=0;
For i In 1..vRecordAmount Loop --每一行
If Mod(i,vColAmount)=0 Then --当读取到足够一行数据的时候
vDealRows:=vDealRows+1;
If vDealRows=1 Then --为了不至于搞错顺序,第一次需要把转列内容填到
--vColNames,也就是例子中的 lzf,wth...等部分
--同时,组合成真正的intosql
For j In 1 ..vColAmount Loop
--vsortAmount表示的是行排序列的个数.
vColNames(j):=vDatas(j)(vGrpColAmount+vSortAmount+1);
--dbms_output.put(lpad( vColNames(j-(vDealRows-1)*vColAmount),20,' '));
vIntoSqls:=vIntoSqls||',C'||to_char(vGrpColAmount+vSortAmount+j);
End Loop;
End If;
v_sSql:='';
For x In 1..vGrpColAmount+vSortAmount Loop --设置值部分分组部分的内容和排序部分内容
v_sSql:=v_sSql||Case x When 1 Then '' Else ',' End||''''||vDatas(i)(x)||'''';
End Loop;
--读取值部分填充值的信息
--真正的值的位置=分组字段个数＋排序列个数＋2
For j In (vDealRows-1)*vColAmount+1..vDealRows*vColAmount Loop
v_sSql:=v_sSql||','||Case When vDatas(j)(vGrpColAmount+vSortAmount+ 2) Is Null Then 'null'
Else ''''||vDatas(j)(vGrpColAmount+vSortAmount+2)||''''
End;
--dbms_output.put(lpad( vDatas(j)(vGrpColAmount+2),20,' '));
End Loop;
--dbms_output.put_line('');
--组合成最后的SQL
v_sSql:=' insert into tempdata_manycols('||vIntoSqls||') values('
||v_sSql||')';
--dbms_output.put_line(v_sSql);
Execute Immediate v_sSql;
End If;
End Loop;
Commit;
--4)形成返回的sql
/*
关键在于知道列名:=分组名称+行转列
*/
/* If rowOrder Is Not Null And rowOrderinGrp=0 Then
vStarColPos:=2;
End If;*/
vResultsql:='select ';
--分别是排序列，和转换列
For i In 1..vGrpColAmount+vColAmount Loop
If i<=vgrpcolamount Then --前一部分的字段别名是分组字段名称,是外部传入的.
vResultsql:=vResultsql||'
'||(Case i When 1 Then '' Else ',' End)||'C'||(i+vSortAmount)||' as '||vRowNames(i);
Else --后面一部分则是由原来行转过来的列的别名.
vResultsql:=vResultsql||'
'||(Case i When 1 Then '' Else ',' End)||'C'||(i+vSortAmount)||' as "'||vColNames(i-vGrpColAmount)||'"';
End If;
End Loop;
If rowOrder Is Null Then
vOrderCols:=getordersql(grpCols);
Else
--vOrderCols:=rowOrder;
--默认只有一个的情况下.正确的情况，应该是另外处理。
vOrderCols:=' c1 ';
End If;
vResultsql:=vResultsql||'
from tempdata_manycols
order by '||vOrderCols;
return(vResultsql);
Exception
When Others Then
Rollback;
Return '';
end func_RowToCol;
测试如下:
SQL> select * from empsalary;
SALMONTH EMPNAME SALARY COUNTRY
---------- -------------------- ---------- --------------------
200801 lzf 8000 中国
200801 wth 8000 美国
200801 lxl 7000 日本
200801 fjl 7000 巴基斯坦
200801 wcl 40000 美国
200802 lzf 9000 中国
200802 wth 8000 美国
200802 lxl 8500 日本
8 rows selected
SQL> Select func_RowToCol(' empsalary ','salmonth,country','empname','salary') From dual;
FUNC_ROWTOCOL('EMPSALARY','SAL
--------------------------------------------------------------------------------
select
C1 as salmonth
,C2 as country
,C3 as "fjl"
,C4 as "lxl"
,C5 as "lzf"
,C6 as "wcl"
,C7 as "wth"
from tempdata_manycols
order by C1,C2
SQL> select
2 cast(C1 As Varchar2(10)) as salmonth
3 ,cast(C2 As Varchar2(10)) as country
4 ,cast(C3 As Varchar2(10)) as "fjl"
5 ,cast(C4 As Varchar2(10)) as "lxl"
6 ,cast(C5 As Varchar2(10)) as "lzf"
7 ,cast(C6 As Varchar2(10)) as "wcl"
8 ,cast(C7 As Varchar2(10)) as "wth"
9 from tempdata_manycols
10 order by C1,C2
11 /
SALMONTH COUNTRY fjl lxl lzf wcl wth
---------- ---------- ---------- ---------- ---------- ---------- ----------
200801 巴基斯坦 7000 0 0 0 0
200801 美国 0 0 0 40000 8000
200801 日本 0 7000 0 0 0
200801 中国 0 0 8000 0 0
200802 美国 0 0 0 0 8000
200802 日本 0 8500 0 0 0
200802 中国 0 0 9000 0 0
7 rows selected