oracle如何实现行列转换(oracle中对坐标进行行列转换)

SELECT行列转换

前几天有一个群友在群里面(190359237)有问到列转换成行的问题，今天终于有时间坐下来查阅资料做一个方法汇总，自己学习一下，也仅以此方式将这些分享给大家。第一部分：行转列新建一个表：CREATE TABLE HANG2LIE( “ID” NUMBER,”NAME” VARCHAR2(20),”COURSE” VARCHAR2(20),”SCORE” NUMBER)在表中插入如下的数据(为了锻炼下pl/sql所以这里费事写了一个PL/SQL程序):declare random_var number; course_var varchar2(20);beginfor i in 1..3 loop for j in 1..5 loop select mod(trunc(dbms_random.value(0,100)*190),100) into random_var from dual; case when j=1 then course_var:=’语文’; when j=2 then course_var:=’数学’; when j=3 then course_var:=’英语’; when j=4 then course_var:=’历史’; when j=5 then course_var:=’化学’; end case; insert into HANG2LIE(id,name,course,score) values(i,’name_’ || i,course_var,random_var); end loop;end loop;end;/数据列出来如下： ID NAME COUR SCORE— ——- —- —– 1 name_1 语文 33 1 name_1 数学 63 1 name_1 英语 71 1 name_1 历史 68 1 name_1 化学 94 2 name_2 语文 85 2 name_2 数学 4 2 name_2 英语 98 2 name_2 历史 9 2 name_2 化学 12 3 name_3 语文 49 3 name_3 数学 96 3 name_3 英语 30 3 name_3 历史 60 3 name_3 化学 2要实现的行转列的效果如下(或者类似的结果)： ID NAME SCORES— ——- ——————– 1 name_1 33,63,71,94,68 2 name_2 85,4,98,12,9 3 name_3 49,2,60,96,301、通过Oracle数据库自带的wm_concat()函数来实现：select id,name,wm_concat(score) scoresfrom HANG2LIEgroup by id,name;2、通过decode函数：select id,name,sum(decode(course,’语文’,score,null)) “语文”,sum(decode(course,’数学’,score,null)) “数学”,sum(decode(course,’英语’,score,null)) “英语”,sum(decode(course,’历史’,score,null)) “历史”,sum(decode(course,’化学’,score,null)) “化学”from HANG2LIEgroup by id,name;得到的结果： ID NAME 语文 数学 英语 历史 化学— ——- ———- ———- ———- ———- ———- 2 name_2 85 4 98 9 12 1 name_1 33 63 71 68 94 3 name_3 49 96 30 60 23、通过case表达式select id,name,sum(case when course=’语文’ then score end) “语文”,sum(case when course=’数学’ then score end) “数学”,sum(case when course=’英语’ then score end) “英语”,sum(case when course=’历史’ then score end) “历史”,sum(case when course=’化学’ then score end) “化学”from HANG2LIEgroup by id,name;得到的结果和第二种实际上是一样的，其实语句也是一样的，只不过把decode函数换成了case when表达式而已

第二部分：列转行

图省力呢，根据上面的表新建一个表：create table lie2hang asselect id,name,sum(case when course=’语文’ then score end) Chinese,sum(case when course=’数学’ then score end) Math,sum(case when course=’英语’ then score end) English,sum(case when course=’历史’ then score end) History,sum(case when course=’化学’ then score end) Chemistryfrom hang2liegroup by id,name;结构如下： ID NAME Chinese Math English History Chemistry— ——- ———- ———- ———- ———- ———- 2 name_2 85 4 98 9 12 1 name_1 33 63 71 68 94 3 name_3 49 96 30 60 2我们要实现如下的查询效果： ID NAME COUR SCORE— ——– —- —– 2 name_2 语文 85 1 name_1 语文 33 3 name_3 语文 49 2 name_2 数学 4 1 name_1 数学 63 3 name_3 数学 96 2 name_2 英语 98 1 name_1 英语 71 3 name_3 英语 30 2 name_2 历史 9 1 name_1 历史 68 3 name_3 历史 60 2 name_2 化学 12 1 name_1 化学 94 3 name_3 化学 21、集合查询实现的SQL语句：select id,name,’语文’ course,chinese score from lie2hangunionselect id,name,’数学’ course,math score from lie2hangunionselect id,name,’英语’ course,english score from lie2hangunionselect id,name,’历史’ course,history score from lie2hangunionselect id,name,’化学’ course,chemistry score from lie2hang;这就是比较常见的列传行操作，主要原理是利用SQL的union集合查询。2、insert all操作语句如下：create table lie2hang_result(id number,name varchar2(20),course varchar2(20),score number);insert allinto lie2hang_result(id,name,course,score) values(id,name,’语文’,chinese)into lie2hang_result(id,name,course,score) values(id,name,’数学’,math)into lie2hang_result(id,name,course,score) values(id,name,’英语’,english)into lie2hang_result(id,name,course,score) values(id,name,’历史’,history)into lie2hang_result(id,name,course,score) values(id,name,’化学’,chemistry)select id,name,chinese,math,english,history,chemistry from lie2hang;这样的结果和第一种方法的相同，不过貌似不大好像起来用这个哈！