drop table score;

create table score(

    name varchar2(10),

    subject varchar2(10),

    grade number(3)

) ;

insert into score values('Zhang','Language',80);

insert into score values('Zhang','Math',92);

insert into score values('Zhang','English',76);

insert into score values('Li','English',50);

insert into score values('Li','Math',95);

insert into score values('Li','Language',81);

insert into score values('Wang','Language',73);

commit;

select * from score;

----将score中数据转换成 name,language,math,english 的形式显示

----方法一：利用组合函数

select name,

decode(subject,'Language',grade)Language,

decode(subject,'Math',grade)Math,

decode(subject,'English',grade)English

from score;

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

select name,

sum(decode(subject,'Language',grade))Language,

sum(decode(subject,'Math',grade))Math,

sum(decode(subject,'English',grade))English

from score

group by name;

-----方法二：利用oracle11g中函数pivot

select * from score

pivot (sum(grade)

       for subject

       in('Language','English','Math'));

1.聚合列取值。需要告诉pivot函数进行转列的过程中，聚合操作的函数和处理对象；

2.行转列标准。依据那个列进行行转列；

3.列转行取值。因为要将数据行取值转成列，我们需要告诉Oracle那些取值成列，并且这些取值成列的过程中，列顺序是如何的；

----将score1中数据转换成 name,subject,gread的形式显示

create table score1 as (select name,

sum(decode(subject,'Language',grade))Language,

sum(decode(subject,'Math',grade))Math,

sum(decode(subject,'English',grade))English

from score

group by name);

select * from score1;

--------方法1

select name, 'Language' object,language grade from score1

UNION ALL

select name, 'Math' object,math grade from score1

union all

select name, 'English' object,english grade from score1

order by name desc;

------方法二

Select * from score1

 Unpivot

 (grade for Subject in(Language,English,Math));

 Select * from score1

 Unpivot  EXCLUDE nulls

 (grade for Subject in(Language,English,Math));

 ----方法三

 drop table score2

 create table score2(

    name varchar2(10),

    subject varchar2(10),

    grade number(3)

) ;

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

insert all

into score2 values(name,'Language',Language)

into score2 values(name,'English',English)

into score2 values(name,'Math',Math)

select name,Language,English,Math

from score1;

select * from score2;