資料庫開發基礎---行列轉換

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;