实现行列间转换需求

在数据库报表查询时事实表中的数据很多情况下没有直接反映最终报表需求的格式，其中很典型的就是要把多行的数据转换成列的形式显示，举例如下：

事实表T_Score（学生成绩表）：

CREATE TABLE "DB2ADMIN"."T_SCORE"  (
    "NAME" VARCHAR(20) ,
    "SUBJECT" VARCHAR(20) ,
    "SCORE" INTEGER ) ;

 

示例数据：

---

 

Mary  Chinese    90
Mary  English     84
Mary  Maths      79
Tom   Chinese    86
Tom   English     93
Tom   Maths      89

---

 

 

但是报表需求：

NAME  Chinese      English      Maths

---

Mary   90             84            79
Tom    86             93            89

---

SQL实现：

select  name,
 sum(decode(subject,'Chinese',score)) as Chinese,
 sum(decode(subject,'English',score)) as English,
 sum(decode(subject,'Maths',score)) as Maths
from t_score group by name;

 

另外还有一种需求是希望把多行的数据合并到一列中，可以利用oracle的树结构查询获得（connect by 语句）：

select sys_connect_by_path(r_name,'|'),level LV from
(select r_name,row_number() over (order by id_stock desc) rn
from t_stocks_fact)  where level>3 connect by rn=prior rn+1;

 

至于列转为行则可以用union all来实现，以T_Score_Row为例，

select name, 'Chinese' as subject, chinese from T_Score_Row

union all

select name, 'English' as subject, english from T_Score_Row

union all

select name, 'Maths' as subject, maths from T_Score_Row

以下SQL是上面行转列过程的逆操作而已。

select * from (
select name, 'Chinese' as subject, chinese from (
select  name,
 sum(decode(subject,'Chinese',score)) as Chinese,
 sum(decode(subject,'English',score)) as English,
 sum(decode(subject,'Maths',score)) as Maths
from t_score group by name)
union all
select name, 'English' as subject, english from (
select  name,
 sum(decode(subject,'Chinese',score)) as Chinese,
 sum(decode(subject,'English',score)) as English,
 sum(decode(subject,'Maths',score)) as Maths
from t_score group by name
)
union all
select name, 'Maths' as subject, maths from (
select  name,
 sum(decode(subject,'Chinese',score)) as Chinese,
 sum(decode(subject,'English',score)) as English,
 sum(decode(subject,'Maths',score)) as Maths
from t_score group by name
)
) order by name