student subject grade
---------------------------
student1 语文 80
student1 数学 70
student1 英语 60
student2 语文 90
student2 数学 80
student2 英语 100
……
转换为
语文 数学 英语
student1 80 70 60
student2 90 80 100
语句如下:
- select student,sum(decode(subject,'语文', grade,null)) "语文",
- sum(decode(subject,'数学', grade,null)) "数学",
- sum(decode(subject,'英语', grade,null)) "英语"
- from table
- group by student
如
c1 c2
--------------
1 我
1 是
1 谁
2 知
2 道
3 不
……
转换为
1 我是谁
2 知道
3 不
这一类型的转换必须借助于PL/SQL来完成,这里给一个例子
- CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER)
- RETURN VARCHAR2
- IS
- Col_c2 VARCHAR2(4000);
- BEGIN
- FOR cur IN (SELECT c2 FROM t WHERE c1=tmp_c1) LOOP
- Col_c2 := Col_c2||cur.c2;
- END LOOP;
- Col_c2 := rtrim(Col_c2,1);
- RETURN Col_c2;
- END;
- SQL> select distinct c1 ,get_c2(c1) cc2 from table;
看到这里,已经有了解决思路了,在oracle中建一个function,代码如下:
- CREATE OR REPLACE FUNCTION get_ver(tmp_boinstid VARCHAR2)
- RETURN VARCHAR2
- IS
- vers VARCHAR2(100);
- BEGIN
- FOR cur IN (SELECT pln_ver FROM PMS_BUDGET_PLAN WHERE boinst_id=tmp_boinstid order by is_all_run) LOOP
- vers := vers||cur.pln_ver||',';
- END LOOP;
- vers := rtrim(vers,1);
- RETURN vers;
- END get_ver;
然后使用这个函数来查询就可以了,
- SELECT distinct p.pln_name,p.pln_year,get_ver(boinst_id) FROM PMS_BUDGET_PLAN p WHERE p.pln_type =20 and p.del_flag = -1 and boinst_id is not null
需要注意的是,由于函数中也使用了sql,所以这个方法不太适合大数据量的查询,使用时应注意。
##################################################################################################################
##################################################################################################################
oracle 动态交叉查询 行列互换 oracle动态交叉表
使用视图 动态创建视图
-
SQL code
-
CREATE OR REPLACE procedure DWDEV.dw_dis_proc(tabname in varchar2,--需要进行行转列操作的表名 group_col in varchar2,--查询结果要按某列或某些列分组的字段名
-
column_col in varchar2,--要从行转成列的字段 value_col in varchar2,--需要聚合的值字段 Aggregate_func in varchar2 default 'max',--选用的聚合函数,可选,默认为max condition in varchar2 default '1=1',--条件语句,可选 colorder in varchar2 default null,--行转列后列的排序,可选 roworder in varchar2 default null,--行转列后记录的排序,可选 when_value_null in varchar2 default null,--若value_col字段的值聚合后为空,则转换成该值,可选 viewname in varchar2 default 'v_tmp'--创建的视图名称,可选,默认为v_tmp ) Authid Current_User as c1 sys_refcursor; v1 varchar2(1000); sqlstr varchar2(10000); countTemp number(10); begin EXECUTE IMMEDIATE 'select count(distinct '||column_col||') from '||tabname into countTemp; sqlstr :='create or replace view '||viewname||' as select '||group_col||',' ||Aggregate_func||'('||value_col||') as "summation",round('|| Aggregate_func||'('||value_col||')/'||countTemp||',2) as "average"'; open c1 for 'select distinct '||column_col||' from '||tabname||' where '||condition|| 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||' where '||condition||' group by '||group_col||case when roworder is not null then ' order by '||roworder end; execute immediate sqlstr; end dw_dis_proc;
select * from v_tmp
tb_score表
转换成:
SQL实现代码:
- create table tb_score
- (
- stu_id int,
- stu_name varchar2(20),
- stu_object varchar2(10),
- stu_score number(6,2)
- )
- go
- select * from tb_score;
- go
- insert into tb_score values(1,'andy','Chinese',80);
- insert into tb_score values(1,'andy','Math',70.05);
- insert into tb_score values(1,'andy','English',90);
- insert into tb_score values(2,'DK','Chinese',80.05);
- insert into tb_score values(2,'DK','Math',73.05);
- insert into tb_score values(2,'DK','English',99);
- go
- select stu_id,stu_name,sum(decode(stu_object,'Chinese',stu_score)) 语文,
- sum(decode(stu_object,'Math',stu_score)) 数学,
- sum(decode(stu_object,'English',stu_score)) 英语 from tb_score
- group by stu_id,stu_name;
- select stu_id,stu_name,sum(case stu_object when 'Chinese' then stu_score end) 语文,
- sum(case stu_object when 'Math' then stu_score end) 数学,
- sum(case stu_object when 'English' then stu_score end) 英语 from tb_score
- group by stu_id,stu_name;
上面两种方式都可以,