SELECT行列转换

原文地址：http://www.2cto.com/database/201108/100792.html

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);

begin

for 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,30

1、通过Oracle数据库自带的wm_concat()函数来实现：

select id,name,wm_concat(score) scores     ---此函数是在wmsys下的，行列转换函数

from HANG2LIE

group by id,name;

---------------------decode---------------------

主要作用：将查询结果翻译成其他值（即以其他形式表现出来，以下举例说明）；

使用方法：

Select decode（columnname，值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值）

From talbename

Where …

其中columnname为要选择的table中所定义的column，

---------------------------------------------

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 HANG2LIE

group 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               2

3、通过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 HANG2LIE

group by id,name;

得到的结果和第二种实际上是一样的，其实语句也是一样的，只不过把decode函数换成了case when表达式而已

第二部分：列转行

图省力呢，根据上面的表新建一个表：

create table lie2hang as

select 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) Chemistry

from hang2lie

group 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    化学       2

1、集合查询

实现的SQL语句：

select id,name,'语文' course,chinese score from lie2hang

union

select id,name,'数学' course,math score from lie2hang

union

select id,name,'英语' course,english score from lie2hang

union

select id,name,'历史' course,history score from lie2hang

union

select 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 all

into 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;

这样的结果和第一种方法的相同，不过貌似不大好像起来用这个哈！