表t如下
userid, 工作时间 是否完成任务
a 2010-3-1 0
a 2010-3-2 1
a 2010-3-4 1
a 2010-3-28 0
b 2010-3-1 0
b 2010-3-21 1
...期待结果userid, 2010-3-1 ,2010-3-2, 2010-3-3 ,...2010-4-21号
a 0 1 空 ... 空
b 0 空 空 ... 空
...生产环境中,提供起始日期和结束日期,横向列是不固定的.lag,lead什么的直接用不适用,拼接或函数也麻烦, 有好办法吗
userid, 工作时间 是否完成任务
a 2010-3-1 0
a 2010-3-2 1
a 2010-3-4 1
a 2010-3-28 0
b 2010-3-1 0
b 2010-3-21 1
...期待结果userid, 2010-3-1 ,2010-3-2, 2010-3-3 ,...2010-4-21号
a 0 1 空 ... 空
b 0 空 空 ... 空
...生产环境中,提供起始日期和结束日期,横向列是不固定的.lag,lead什么的直接用不适用,拼接或函数也麻烦, 有好办法吗
create or replace procedure row_to_col(tabname in varchar2,
group_col in varchar2,
column_col in varchar2,
value_col in varchar2,
Aggregate_func in varchar2 default 'max',
colorder in varchar2 default null,
roworder in varchar2 default null,
when_value_null in varchar2 default null,
viewname in varchar2 default 'v_tmp')
Authid Current_User
as
sqlstr varchar2(2000):='create or replace view '||viewname||' as select '||group_col||' ';
c1 sys_refcursor;
v1 varchar2(100);
begin
open c1 for 'select distinct '||column_col||' from '||tabname||case when colorder is not null then ' order by '||colorder end;
loop
fetch c1 into v1;
exit when c1%notfound;
sqlstr:=sqlstr||chr(10)||','||case when when_value_null is not null then 'nvl(' end||
Aggregate_func||'(decode(to_char('||column_col||'),'''||v1||''','||value_col||'))'||
case when when_value_null is not null then chr(44) ||when_value_null||chr(41) end||'"'||v1||'"';
end loop;
close c1;
sqlstr:=sqlstr||' from '||tabname||' group by '||group_col||case when roworder is not null then ' order by '||roworder end;
execute immediate sqlstr;
end row_to_col;
这里修改了传入参数名,使其更容易理解。继续使用了创建视图这个方法,当然也可以改成用游标传出。
参数:
tabname 需要进行行转列操作的表名;
group_col 查询结果要按某列或某些列分组的字段名;
column_col 要从行转成列的字段;
value_col 需要聚合的值字段;
Aggregate_func 选用的聚合函数,可选,默认为max;
colorder 行转列后列的排序,可选;
roworder 行转列后记录的排序,可选;
when_value_null 若value_col字段的值聚合后为空,则转换成该值,可选;
viewname 创建的视图名称,可选,默认为v_tmp。 举例: --测试数据
create table rowtocol_test as
select 2009 year,1 month,'部门1' dept,50000 expenditure from dual
union all select 2009,2,'部门1',20000 from dual
union all select 2009,2,'部门1',30000 from dual
union all select 2010,1,'部门1',35000 from dual
union all select 2009,2,'部门2',40000 from dual
union all select 2009,3,'部门2',25000 from dual
union all select 2010,2,'部门3',60000 from dual
union all select 2009,2,'部门3',15000 from dual
union all select 2009,2,'部门3',10000 from dual;
我现在想根据year和month分组,将部门转成列。 SQL> select * from rowtocol_test;
YEAR MONTH DEPT EXPENDITURE
---------- ---------- ----- -----------
2009 1 部门1 50000
2009 2 部门1 20000
2009 2 部门1 30000
2010 1 部门1 35000
2009 2 部门2 40000
2009 3 部门2 25000
2010 2 部门3 60000
2009 2 部门3 15000
2009 2 部门3 10000
9 rows selected
SQL> execute row_to_col('rowtocol_test','year,month','dept','expenditure');PL/SQL procedure successfully completed
SQL> select * from v_tmp;
YEAR MONTH 部门1 部门3 部门2
---------- ---------- ---------- ---------- ----------
2009 1 50000
2010 1 35000
2009 3 25000
2009 2 30000 15000 40000
2010 2 60000
SQL>
这个结果可能不是我们想要的,重新调用过程,使用几个可选参数
SQL> execute row_to_col('rowtocol_test','year,month','dept','expenditure',Aggregate_func => 'sum',colorder => 'dept',roworder => '1,2',when_value_null => '0');
PL/SQL procedure successfully completed
SQL> select * from v_tmp;
YEAR MONTH 部门1 部门2 部门3
---------- ---------- ---------- ---------- ----------
2009 1 50000 0 0
2009 2 50000 40000 25000
2009 3 0 25000 0
2010 1 35000 0 0
2010 2 0 0 60000
SQL> 进行行转列的也可以是视图 SQL> create view view_rowtocol as select * from rowtocol_test where year=2009;
View created
SQL> execute row_to_col('view_rowtocol','year,month','dept','expenditure',Aggregate_func => 'sum',colorder => 'dept',roworder => '1,2',when_value_null => '0');
PL/SQL procedure successfully completed
SQL> select * from v_tmp;
YEAR MONTH 部门1 部门2 部门3
---------- ---------- ---------- ---------- ----------
2009 1 50000 0 0
2009 2 50000 40000 25000
2009 3 0 25000 0
SQL>
如果有问题,自己调试一下。--i_beginDate:开始时间; i_endDate:结束时间
CREATE OR REPLACE PROCEDURE row2col(i_beginDate DATE, i_endDate DATE, o OUT SYS_REFCURSOR) IS
sqlstr VARCHAR2(20000) := '';
BEGIN
FOR cc IN (SELECT LEVEL + i_beginDate - 1 evdate
FROM dual
CONNECT BY LEVEL <= trun(i_endDate) - (trunc(i_beginDate)) + 1) LOOP
sqlstr := sqlstr || 'max(decode(工作时间, date''' || to_char(cc.evdate, 'yyyy-mm-dd') ||
''',是否完成任务)) as "' || cc.evdate || '",';
END LOOP;
sqlstr := 'select userid,' || rtrim(sqlstr, ',') || ' from t group by userid';
OPEN o FOR sqlstr;
END row2col;
pivot(
,max(是否完成任务) for 工作时间 in ('2010-3-1' as 2010-3-1,
'2010-3-2' as 2010-3-2,
'2010-3-3' as 2010-3-3,
--有多少加多少
)
)
order by userid;
sqlstr VARCHAR2(1800) := '';
BEGIN
FOR cc IN (SELECT LEVEL + i_beginDate - 1 evdate
FROM dual
CONNECT BY LEVEL <= trunc(i_endDate) - (trunc(i_beginDate)) + 1) LOOP
sqlstr := sqlstr || 'max(decode(工作时间, date''' || to_char(cc.evdate, 'yyyy-mm-dd') ||
''',是否完成任务)) as "' || to_char(cc.evdate, 'yyyy-mm-dd') || '",';
END LOOP;
sqlstr := 'select userid,' || rtrim(sqlstr, ',') || ' from t group by userid';
OPEN o FOR sqlstr;
END row2col;--出来的sqlstr的语句形如,要用日期做为字段名,必须要加双引号的,否则字段名不能以数字开头
SELECT userid,
MAX(decode(工作时间, DATE '2010-03-01', 是否完成任务)) AS "2010-03-01",
MAX(decode(工作时间, DATE '2010-03-02', 是否完成任务)) AS "2010-03-02",
MAX(decode(工作时间, DATE '2010-03-03', 是否完成任务)) AS "2010-03-03",
MAX(decode(工作时间, DATE '2010-03-04', 是否完成任务)) AS "2010-03-04",
MAX(decode(工作时间, DATE '2010-03-05', 是否完成任务)) AS "2010-03-05"
FROM t
GROUP BY userid