SQL查询实现行转列

 

1.  在scott用户下创建一张表test表的结构如下：

create table test(name varchar(10),subject varchar(10),score number)此表记录姓名，科目和成绩自动

2.   用sqlldr 导入如下数据：

 Name   subject   score

 Jack  ,  maths  ,  89

 Jack  ,  Chinese ,  90

 Jack  ,  English ,  78

 Sala  ,  maths ,  99

 Sala  ,  Chinese , 97

 Sala  ,  English , 87

构造一个a.txt文件存放上述数据，存放c:\a.txt下

3.  写一个控制文件内容如下：

Control.ctl

LOAD DATA

INFILE 'c:\a.txt'

APPEND

INTO TABLE test

FIELDS TERMINATED BY "," TRAILING NULLCOLS

(

   name,subject,score

)

4.       用sqlldr把数据导入到数据库中

Sqlldr scott/tiger control=’c:\control.ctl’

查询表test显示如下：

SQL> select * from test;

NAME                 SUBJECT                   SCORE

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

Jack                 maths                        89

Jack                 Chinese                      90

Jack                 English                      78

Sala                 maths                        99

Sala                 Chinese                      97

Sala                 English                      87

已选择6行。

5．写一个查询语句把行转为列显示如下

Name  maths   Chinese  English

语句如下：

select name , sum(decode(subject,'maths',score,0)) as maths,sum(decode(subject,'Chinese',score,0)) as Chinese ,sum(decode(subject,'English',score,0)) as english from test group by name;

SQL> select name , sum(decode(subject,'maths',score,0)) as maths,sum(decode(subject,'Chinese',score,0)) as chine

se

  2  ,sum(decode(subject,'English',score,0)) as english from test group by name;

NAME                      MATHS    CHINESE    ENGLISH

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

Jack                         89         90         78

Sala                         99         97         87

完成行列的操作